st: Re: Using OBDC to reference a Stata file?

["Joseph Coveney" <stajc2@gmail.com>]

Jeph Herrin wrote:

I'm trying to use Stata to construct tables from a very large database (ie,
terrabytes) using ODBC.  I'm new to SQL, but because of the size of the
database am trying to do as much as I can via the SQL requests to avoid
working with very large files. 

Currently, I am able to create, say, table1.dta which is a list of
identifiers that meet a certain criteria; this results from a very complex
SQL statement that I submit via 

. odbc, exec(`mySQL')  
. save table1

Now I would like to use table1 in my next SQL query so I select only those
identifiers. Possible? 

It seems not, but I feel compelled to ask because the advice I have received
from those who routinely use this database is to use SAS, which will in fact
let me reference SAS datasets in an SQL procedure. I'd prefer not to.

--------------------------------------------------------------------------------

I believe that you cannot reference a Stata dataset in -odbc-.  In addition to
the approach that Sergiy mentioned, would it be possible to get the
administrator to set you up with the ability to create temporary tables
corralled off in, say, your own schema set up for you in inside the database by
the administrator?  That would probably be the best way:  you can place indexes
on the table for best efficiency, and a temporary table would, as Sergiy warned,
protect the contents of your Table1 against updates made to the source table(s)
in the database in the interim.  

It's my understanding that, despite the minor convenience of being able to
reference both SAS datasets and database tables transparently as LIBNAMEs, the
current thinking is to do as much processing as possible in the database,
itself, by use of pass-through queries.  You might want to check with your SAS
colleagues about this before leaping; pass-through queries are what Stata's
-odbc- does.

Joseph Coveney

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/