Welcome to RunSubmit.com

Question

3

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.

flag	asked Oct 21 at 15:15 Dagan 131●3

Alan Churchill · Answer 1 · 2009-10-21 15:33:24Z UTC

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.

Alan

http://www.savian.net

jay.l.stevens · Answer 2 · 2009-10-21 17:36:59Z UTC

That's definitely a challenge. Here are my thoughts:

I have found that DBF is a fairly stable transport format for importing into SAS; at least you don't have to do explicit type conversions. The variable names are a problem though (as you've mentioned).
Another option is to use the for XML at the end of the SQL statement to generate XML for the entire data structure. If the size of the intermediate format isn't an issue, then you may be able to map the resulting XML into the SAS dataset structure you want.
You may also be able to use SQLCMD and a Filename PIPE to stream the data directly from the sqlcmd.exe into SAS for parsing.

PeterC · Answer 3 · 2010-06-22 21:10:38Z UTC

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.

Using SAS Base to Import Data from tables in SQL Server using ODBC?

Remember to vote up questions/answers you find interesting or helpful (requires 15 reputation points)

3 Answers