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

Dagan's gravatar image

Dagan
12926
accept rate: 0%


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

link

answered 21 Oct '09, 15:33

Alan%20Churchill's gravatar image

Alan Churchill
26922
accept rate: 44%

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.

link

answered 21 Oct '09, 17:36

jay.l.stevens's gravatar image

jay.l.stevens ♦
1.0k624
accept rate: 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.

link

answered 22 Jun '10, 21:10

PeterC%201's gravatar image

PeterC 1
311
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×19
×7
×3

Asked: 21 Oct '09, 15:15

Seen: 4,031 times

Last updated: 22 Jun '10, 21:10

powered by OSQA