I am again learning to use SAS after working in Stata, R, and SPSS, along with SQL Server, etc.
Without having to pay an expensive add-on using SAS/ACCESS interface to ODBC or PC file formats, how can I use/write code to directly import via ODBC data from a SQL Server on the database tables?
The tables/file size are 1,000,000 + records with about 130 variables. I have been able to link tables in Access and then save as an csv file then import. I also tried dbf (but run into issues with the longer variable names). CSV imports were inconsistant on imported formats for the same variable with char vs. num formats.
Is there code I can do a direct connect/pull into SAS myself from SQL Server. I have the ODBC set up on the computer and it works well.
I am just learning/trying to work within the SAS framework, but work within our org cost constraints. Thank-you for all your feedback ahead of time.
asked 21 Oct '09, 15:15
You need SAS/Access to OleDB or SAS/Access to ODBC. There are ways outside of that but it gets a lot more convoluted. If you absolutely ave to avoid that route, export as tab-delimited and bring it in that way. Formats are problematic. If you know .NET and C#, you can use my libraries to simplify it a bit. XML is also a choice.
My SAS dataset reader is not ready so it isn't an option right now.
answered 21 Oct '09, 15:33
That's definitely a challenge. Here are my thoughts:
answered 21 Oct '09, 17:36
if you are still stuck with no hope of a new sas/access module to directly read the data, consider programming a vba macro for excel to load (say) "the next" 50k rows. Then you could also program base SAS to run that macro and read the range through a DDE interface. That comes at no extra charge with all windows applications. However, you might wonder why DDE is "deprecated" technology.
answered 22 Jun '10, 21:10