Solved

How to import SAS files to Microsoft SQL Server

Posted on 2010-08-27
24
3,743 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
24 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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 50 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 142

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

The curse of the end user strikes again      

You’ve updated all your end user’s email signatures. Hooray! But guess what? They’re playing around with the HTML, adding stupid taglines and ruining the imagery. Find out how you can save your signatures from end users today.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now