I recently discovered that Stata has good support for OBDC. While the built-in functionality includes odbc sqlfile, a “batch job alternative to odbc exec,” there seems to be no equivalent alternative for odbc load (even though odbc load is surely the more useful function).
But this is easily addressed using an .ado program I adapted from that discussed in an earlier post.
I created a file (loadsql2.ado) containing the following:
program define loadsql2
*! Load the output of an SQL file into Stata, version 1.6 (iandgow@gmail.com)
version 13.1
syntax using/, DSN(string)
#delimit;
tempname sqlfile exec line;
file open `sqlfile' using `"`using'"', read text;
file read `sqlfile' `line';
while r(eof)==0 {;
local `exec' = "``exec''
``line''";
file read `sqlfile' `line';
};
file close `sqlfile';
* display "``exec''";
odbc load , exec("``exec''") dsn("`dsn'") clear;
end;
I placed this file in ~/Library/Application\ Support/Stata/ado/personal.
Since I have a ODBC DSN iangow set up, I can now issue the following (see my earlier post for details on ~/some_sql.sql):
loadsql2 using "~/some_sql.sql", dsn("iangow")
and then I have data in Stata:
. list in 1/5
+--------------------------------------+
| gvkey datadate at ni |
|--------------------------------------|
1. | 001073 31dec1985 3.095 .541 |
2. | 001073 31dec1986 4.422 .584 |
3. | 001073 31dec1987 3.9 -.359 |
4. | 001074 31dec1970 34.856 1.716 |
5. | 001073 31dec1988 2.067 -1.191 |
+--------------------------------------+
iangow 