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

["Dimitriy V. Masterov" <dvmaster@gmail.com>]

Abe,

I load SQL data via odbc without any hiccups, and at first I spent a
fair amount of time repeating calculations in SQL and Stata to make
sure I was doing things correctly.

Could you replicate the error with a simple query like "select top 5
record_id from ..."? It might help if we see the code and the output.
I would recommend solving this rather than the insheet approach. Also,
which odbc manager are you using?

DVM

On Wed, Jan 23, 2013 at 1:06 PM, Abe N <aben468@yahoo.com> wrote:
> 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/

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