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

[<stata@spandrel.net>]

Thanks for the suggestion, but I think temporary tables are out for now,
though I will be pursuing this as a possibility in the future.  

Cheers,
J

-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu
[mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Joseph Coveney
Sent: Monday, September 30, 2013 11:32 PM
To: statalist@hsphsun2.harvard.edu
Subject: st: Re: Using OBDC to reference a Stata file?

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/

*
*   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/