Bookmark and Share

Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

st: ODBC vs. insheet variable format troubles (importing SQL data)


From   Abe N <aben468@yahoo.com>
To   statalist@hsphsun2.harvard.edu
Subject   st: ODBC vs. insheet variable format troubles (importing SQL data)
Date   Wed, 23 Jan 2013 13:06:31 -0800 (PST)

Hello Everyone,

Basically wondering if there is a way I can conserve variable data formats when I bring data manually from SQL to STATA using insheet (see below for more explanation and trouble I had with ODBC).

I was originally told by the people maintaining the SQL database to use: 

odbc load, exec("Select command here") dsn("databasename") lowercase

to bring data into STATA, which seemed to be working great for my purposes until I did a duplicates report and found that I had way too many duplicates.  After cross-checking by record_id (unique) with the original SQL data I found the odbc command wasn't importing data properly.  I had already set up the variable list though (some were too long so I had to manually rename them), so I used describe and I think display "`r(varlist)'", copied that varlist to a text file for what I would do in my next step using insheet.

Basically, ran my SQL query in SQL Server Management Studio 2008 R2, saved the results/data as a tab delimited text and used:

insheet (list of 160 variables) using data.txt, tab

Which seems to be working great.  The only problem I'm having now is that while odbc maintained the variable data formats (string vs double, etc from SQL), this new method doesn't and it would be much easier for me if it didn't convert some of my strings into numbers, etc.

So what I'm wondering is if there's a way I can sort of generate the variables, their data formats, and labels using my original odbc method, clear out that incorrect data, and then pull in data from the text file afterward?  If not, is there a way I can carry over at least the variable labels quickly rather than manually doing all 160 or so?

Sorry for the long winded explanation, but hope the situation/question gets across clearly.

Best,
Abe Noorbakhsh
aben468@yahoo.com
*
*   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/


© Copyright 1996–2018 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   Site index