How to import SAS files to Microsoft SQL Server

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

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.

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.

>>Have you looked at using the SAS ODBC drivers?  

See comment

ID: 33546514

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

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

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.

wow.  this is even less helpful.  Let me not waste your time.  

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.

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

You don't need to access SAS or own the software.

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

the example from burrcm shows the use of the ODBC driver from SAS in action.

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

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

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

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.

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

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

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.