Solved

How to import SAS files to Microsoft SQL Server

Posted on 2010-08-27
24
4,018 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Know what services you can and cannot, should and should not combine on your server.
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…

808 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