?
Solved

How to import SAS files to Microsoft SQL Server

Posted on 2010-08-27
24
Medium Priority
?
4,745 Views
Last Modified: 2013-11-16
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.
0
Comment
Question by:omerartun
  • 4
  • 4
  • 4
  • +5
20 Comments
 
LVL 16

Expert Comment

by:carsRST
ID: 33546514
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
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 33547733
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
 
LVL 5

Expert Comment

by:tobey1
ID: 33569270
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:carsRST
ID: 33569277
>>Have you looked at using the SAS ODBC drivers?  

See comment

ID: 33546514
0
 

Author Comment

by:omerartun
ID: 33572168
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
 
LVL 16

Expert Comment

by:carsRST
ID: 33572272
>>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
 
LVL 16

Expert Comment

by:carsRST
ID: 33572282
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
 

Author Comment

by:omerartun
ID: 33572299
wow.  this is even less helpful.  Let me not waste your time.  
0
 
LVL 5

Expert Comment

by:tobey1
ID: 33572340
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
 

Author Comment

by:omerartun
ID: 33572403
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
 
LVL 5

Expert Comment

by:tobey1
ID: 33572522
You don't need to access SAS or own the software.
0
 
LVL 28

Expert Comment

by:burrcm
ID: 33574834
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
 
LVL 5

Expert Comment

by:tobey1
ID: 33576322
the example from burrcm shows the use of the ODBC driver from SAS in action.
0
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 33576390
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
 

Author Comment

by:omerartun
ID: 33613322
http://einstein.library.emory.edu/SAS_to_other.shtml  

tried this before.  It corrupts large files, not a solution.
0
 
LVL 3

Expert Comment

by:radevo
ID: 33664487
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
 
LVL 11

Accepted Solution

by:
theartfuldazzler earned 200 total points
ID: 33830182
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
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 35998145
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
 
LVL 11

Expert Comment

by:theartfuldazzler
ID: 36001022
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36178720
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question