Load SAS Dataset to MS SQL Server via SQL server job

[Quentin]

Hi All,

Looking for options/guidance on best ways to load a SAS dataset into a SQL server database, having the load process be executed by SQL server (SSIS or whatever) rather than as a SAS job.

The person loading the data does have a PC SAS license (and access to a SAS BI server), so it looks like the SAS ODBC driver may be an option.  I also saw this post on using OLE http://tech.lanesnotes.com/2012/06/importing-sas7bdat-file-into-sql-server.html .

I know we could write a SAS job to load the data.  And may eventually go that way. 

But currently this DBA manages all the data loads with non-SAS tools, and we only connect to the database from SAS to read data.  Now the DBA has a project where a SAS dataset will arrive weekly and need to be loaded automatically.  I'm hoping to recommend to him a way he could load the data that he would be comfortable with, and be able to manage the load using tools he is already familiar with (SQL server management studio, etc), rather than introduce the execution of SAS jobs into his processes.

Run-time efficiency is not a critical issue, as will only be loading two tables, and will be less than a million records.

Thanks,

--Q.

[LinusH]

It's not 100% clerk from that post ifor SAS was installed locally but that is my assumption.
But I haven't tried it and would really like to know if this can accomplished without a SAS session.
There have been a number of threads discussing the possibility to read SAS data without SAS, and this concept didn't come up.

[Quentin]

Agree.  In my brief reading everything suggests you need a SAS session (local or remote) for both ODBC and OLE, e.g.

http://support.sas.com/documentation/cdl/en/oledbpr/63701/HTML/default/viewer.htm#titlepage.htm

In my current use-case I'm oaky with using a SAS session, as long as the SAS session is mostly transparent to the DBA.  That is, I don't want them to be writing SAS code or feel like they are managing a SAS job.

But I absolutely agree, it would be great to have a way to allow ODBC access  (or similar) to SAS datasets without needing a SAS session.  Since SAS gives away the SAS Universal Viewer for reading SAS data sets, doesn't seem like it should be that hard to make the SAS data available to non-SAS applications, without requiring a full SAS session.

Googling turns up lots of recommendations to dump the SAS dataset to CSV, or play with scripting some mix of third-party solutions (cozyroc, stat transfer, R etc).

[SASKiwi]

A couple of suggestions:

• Use SAS to load a temporary SQL Server table which is then used by the SQL Server load process.
• Write a delimited file from SAS then get the SQL Server load process to read the delimited file (already mentioned by @Quentin)

[Quentin]

Thanks @SASKiwi I'm hoping to avoid writing data to CSV and losing all the metadata. But if the DBA can't come up with a nice ODBC/OLE solution, I may try using SAS to load the data to a temp SQL server staging table. The down side is it would keep SAS code in the middle of the ETL process. But at least it would keep the DBA in charge of loading the data from the staging table into the main tables. And they could manage any data validation/integrity constraints/error trapping when they do that load.

[LinusH]

Ok, so from an architectural point of view I would recommend that you store the SAS data on the SAS Server, and you access it from SSIS using OLEDB or ODBC. If the SAS server is remote to SSIS you need to investigate if there's additional licenses required, such as SHARE and SHARE*NET.

[Ksharp]

Can you make a SHELL or BAT file and let SQL Admin execute it ?

[Quentin]

Thanks @Ksharp. You mean a BAT file to execute a SAS program to load the data? Yes, I could, but my goal is to remove SAS from the process. That is, if a DBA is asked to load a SAS dataset into a MS SQL Server database and does not have access to a SAS programmer, what is the best way for them to do it? This question comes up a lot on the 'net, but I haven't seen a good answer.

Has anyone had success with using ODBC or OLE connections to read data from SAS datasets into SQL Server? Are there good reasons to prefer one or the other?

[Tom]

You could get a utilty tool such as STAT/Transfer for the DBA to use. They could use that to then deal with many sources of data, not just SAS datasets.

You could also look at R and Python utilities that can read SAS datasets.

[Quentin]

If I changed the question to how to load a SAS Transport file (instead of a native SAS dataset) into MS SQL Server, without SAS code or a third-party application, would that make life easier for the DBA? 

My vague memory is that the format of SAS Transport files is not proprietary, so it's possible there may be free ODBC drivers provided by SAS (or others) for reading data from them??

[LinusH]

Not sure if I understand now, why is it necessary to remove SAS from the flow? It's still SAS data, and you would still need SAS to (re-) create it...?

The only transport format that is somewhat open is the V6 xport engine. Doesn't sounds like a futuristic approach to me.

[Quentin]

Thanks @LinusH.  If changing to tranpsort files would make our DBA's life easier on the load side, my thought was to ask the data provisioner to change to send tranpsort files instead of .sas7bdat. 

But I hadn't realized that only the v6 xport files were open.  I'm not going to ask the provisioner to go back to 8 character var names etc.

[LinusH]

If you can consider transport files I think you could also accept csv files. Should be quite similar work effort.

[Quentin]

Sure, but with csv you lose metadata and have to think about maintaining precision.  So maybe XML to keep the metadata with the file...

So yeah, there are alternatives to delivering SAS datasets, but the core question remains, if a non-SASsy DBA receives a SAS dataset, what is the best/easiest way for them to read it into their database?

I'm casually aware that there are R /python / etc folks who have tried to read SAS datasets with varying success (and updates with SAS versions etc),  and I would guess cozyroc has written their own ODBC diver to do similar (?).   Then as mentioned, stat/transfer and formerly DBMS copy are probably scriptable.  But having outsiders reverse-engineer this can only work so well.   I see a lot of posts online where a DBA is asking "what the #$?!!  can I do with this SAS dataset that was dropped in my hands."

I'm fine with SAS having a closed/proprietary format for storing data. But would be nice to have an open transport format as well that maintained variable names, labels, formats, and numeric precision.  Even if it was less efficient, and perhaps didn't have all the bells and whistles of a native SAS dataset (integrity constraints, audit trails, indexes, etc.). 

[LinusH]

I'm not aware ox that any other RDBMS has an open transport format, so why would SAS have it?
XML is fine unless you have large data sets, it comes with quite an overhead. And IMO not that easy to create and maintain...
Can't see the problem really with csv files. Many DW are based on flat file imports. The file format is agreed upon in a separate file specification (that's your meta data).
If you want to have the cookie and eat it...i think you need to accept a direct ODBC connection.