How to import SAS files to Microsoft SQL Server

A client sends us files in SAS file format
and we are trying to find a way to import it into SQL Server in an automated fashion.   We do not have SAS in house, and we don't want to buy it either.  We just want to import the data into sql server.
omerartunAsked:
Who is Participating?
 
theartfuldazzlerConnect With a Mentor Commented:
Hi

After some searching the net and SAS website, there does appear to be a way to import a SAS dataset using Microsoft ADO.

 http://support.sas.com/documentation/cdl/en/oledbpr/59558/PDF/default/oledbpr.pdf

Steps to get this to work...

1.  Download and install the relevant file from http://www.sas.com/apps/demosdownloads/setupintro.jsp . Click SAS Providers for OLE DB.  Download the latest version - you will probably need to register for free.

2. In the VB editor (with a project open), select Tools > References and select the relevant Microsoft ActiveX Data Object Librarys, and anything that appears as "SAS"

The attached VB code imported a small sas data set called "Formats.sas7bdat" in folder "c:\sasData" into excel, using the SAS local provider.


I'm still figuring this all out - but hopefully this gives you a start to importing without having to buy expensive licences


Sub Test()

Dim obConnection As New ADODB.Connection
Dim obRecordset As New ADODB.Recordset
Dim obCommand As New ADODB.Command

obConnection.Provider = "sas.LocalProvider"
obConnection.Properties("Data Source") = "c:\SASData"
obConnection.Open


obRecordset.Open "formats", obConnection, adOpenStatic, adLockReadOnly, adCmdTableDirect


Cells(1, 1).Select$
   For i = 0 To obRecordset.Fields.Count - 1
      ActiveCell.Offset(0, i).Value = obRecordset.Fields(i).Name
   Next i
   
 obRecordset.MoveFirst
   Cells(2, 1).Select
   ActiveCell.CopyFromRecordset obRecordset

   'CLEANUP
   obRecordset.Close
   Set obRecordset = Nothing
   obConnection.Close
   Set obConnection = Nothing
End Sub

Open in new window

0
 
carsRSTCommented:
From an automated fashion, you'll probably want to use SSIS and schedule with SQL Server agent.

The link below provides assistance on what you'll need as a dataprovider, so that SSIS can connect/talk with SAS.

http://support.sas.com/documentation/tools/oledb/index.htm
0
 
Aloysius LowCommented:
AFAIK, there's no way this can be done.

i would suggest that you request for the file be exported as a delimited file for import.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
tobey1Commented:
Have you looked at using the SAS ODBC drivers?  

http://support.sas.com/techsup/technote/ts626.html

You should be able to access the SAS data and pull it into SQL Server.
0
 
carsRSTCommented:
>>Have you looked at using the SAS ODBC drivers?  

See comment

ID: 33546514
0
 
omerartunAuthor Commented:
ok, i was looking for some experts, clearly there are none around here.  here is a company that has the tool  http://www.teamwpc.co.uk/products/wps  the problem is they are being sued by SAS.  There should be another solution since R and other stat programs clearly can import data from SAS files.

For all who answered to have me look at ODBC drivers of SAS, clearly have not read my question properly.

Thank you
0
 
carsRSTCommented:
>>For all who answered to have me look at ODBC drivers of SAS, clearly have not read my question properly.

Read your question fine.  You asked how to import in an automated fashion.  My response (of which this is the last) answered how to do that.  If the responses are too technical, then you're probably better off requesting a consultant come in and perform the task for you.

Also, as I mentioned, automated imports with SQL Server are typically done using SSIS.
0
 
carsRSTCommented:
Sorry - guess this is the last post.

Upping the point total will get you a better response.  I typically don't waste time with 100 or less.
0
 
omerartunAuthor Commented:
wow.  this is even less helpful.  Let me not waste your time.  
0
 
tobey1Commented:
WPS is a waste of money, trust me on that.
Many other users and companies have mar use of the ODBC drivers.  It really isn't that hard to implement.  Add the ODBC driver      and write a simple program in your favorite language and you are done.
0
 
omerartunAuthor Commented:
I don't have access to the SAS software or the server it's on.  The client wants to FTP me the files.  I do NOT have access to SAS software
0
 
tobey1Commented:
You don't need to access SAS or own the software.
0
 
burrcmCommented:
OK. Clearly you are not going to access a proprietory data format without access to the program (of which I know nothing), so a conversion at the source is required. There is a walkthrough here which purports to provide the required details.

http://einstein.library.emory.edu/SAS_to_other.shtml

Good luck

Chris B
0
 
tobey1Commented:
the example from burrcm shows the use of the ODBC driver from SAS in action.
0
 
Aloysius LowCommented:
to which i iterate my original answer - that as far as i know, this cannot be done IF purchasing SAS is not an option.

could you negotiate for a delimited file to be sent? then you can load using SQL's bulk loader
0
 
omerartunAuthor Commented:
http://einstein.library.emory.edu/SAS_to_other.shtml  

tried this before.  It corrupts large files, not a solution.
0
 
radevoCommented:
Unfortunately, SAS bought out the third-party DBMS/COPY product years ago.  Alan at Savian.net announced an Alpha version of a SAS data file reader back in Feb. 2010, but I don't know the status of that now.

In order to use SAS ODBC drivers to read SAS datasets, you need a access to a SAS server somewhere (see http://support.sas.com/documentation/cdl/en/odbcdref/63284/PDF/default/odbcdref.pdf).

If you look hard enough, or just call SAS, they may still have available a universal ODBC drvier for read-only access for systems without SAS. If I recall the previous such was distirbuted as trialware and timed out eventually.

If you do not (and it sounds like you don't and in the future won't) have SAS, a band-aid solution is to use the free SAS Universal Viewer (http://www.sas.com/apps/demosdownloads/setupcat.jsp?cat=Base+SAS+Software) to copy and paste (or perhaps File/Save As) the data into a file that your SQL Server scripts will read.  Production use of the viewer may be against the download license terms (I'm no expert on that)

The all around best solution is to have your client send you the data in a form you can readily use.  There are numerous easy ways to export data from SAS to forms such as csv, tab delim, xml, etc...

If your client won't, then as a data processor, you will have to bite the bullet and buy a SAS license and roll it into your overhead.
0
 
Aloysius LowCommented:
unless theartfuldazzler and the rest of the experts disagree, i would say that officially there's no way this can be done, hence, my comment http://#33547733 should be awarded full points.

of course, i haven't tried theartfuldazzler's recommendations, and neither has the asker posted back to say if this is indeed working or not, and furthermore, it is not known if this will really solve the problem...
0
 
theartfuldazzlerCommented:
Hi

The solution I gave does work, and doesn't require a purchase of SAS licences.  I feel its a valid solution - but we have had no comment from omeratun to confirm.

If no confirmation is received, I would ask that points be granted to the solution I gave.

Regards
theartfuldazzler
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.