Executing SQL in a file against an ODBC source from Stata

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)
tempname sqlfile exec line;
file open `sqlfile' using `"`using'"', read text;
file read `sqlfile' `line';
while r(eof)==0 {;
    local `exec' = "``exec''
    file read `sqlfile' `line';
file close `sqlfile';
* display "``exec''";
odbc load , exec("``exec''") dsn("`dsn'") clear;

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 |
This entry was posted in Mac, PostgreSQL, Stata. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s