topic Re: Load SAS Dataset to MS SQL Server via SQL server job in SAS Data Management

Load SAS Dataset to MS SQL Server via SQL server job

Quentin — Tue, 09 Aug 2016 14:04:29 GMT

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.

Re: Load SAS Dataset to MS SQL Server via SQL server job

LinusH — Tue, 09 Aug 2016 14:23:06 GMT

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.

Re: Load SAS Dataset to MS SQL Server via SQL server job

Quentin — Tue, 09 Aug 2016 15:13:11 GMT

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

Re: Load SAS Dataset to MS SQL Server via SQL server job

LinusH — Tue, 09 Aug 2016 17:32:51 GMT

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.

Re: Load SAS Dataset to MS SQL Server via SQL server job

SASKiwi — Tue, 09 Aug 2016 19:37:03 GMT

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)

Re: Load SAS Dataset to MS SQL Server via SQL server job

Ksharp — Wed, 10 Aug 2016 02:02:50 GMT

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

Re: Load SAS Dataset to MS SQL Server via SQL server job

Quentin — Wed, 10 Aug 2016 14:22:03 GMT

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?

Re: Load SAS Dataset to MS SQL Server via SQL server job

Quentin — Wed, 10 Aug 2016 14:27:23 GMT

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.

Re: Load SAS Dataset to MS SQL Server via SQL server job

Tom — Wed, 10 Aug 2016 14:44:25 GMT

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.

Re: Load SAS Dataset to MS SQL Server via SQL server job

Quentin — Wed, 10 Aug 2016 14:58:08 GMT

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

Re: Load SAS Dataset to MS SQL Server via SQL server job

LinusH — Thu, 11 Aug 2016 14:49:55 GMT

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.

Re: Load SAS Dataset to MS SQL Server via SQL server job

Quentin — Thu, 11 Aug 2016 16:09:08 GMT

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.

Re: Load SAS Dataset to MS SQL Server via SQL server job

LinusH — Thu, 11 Aug 2016 19:43:55 GMT

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

Re: Load SAS Dataset to MS SQL Server via SQL server job

Quentin — Thu, 11 Aug 2016 20:18:33 GMT

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

Re: Load SAS Dataset to MS SQL Server via SQL server job

LinusH — Thu, 11 Aug 2016 20:47:35 GMT

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.

Re: Load SAS Dataset to MS SQL Server via SQL server job

Quentin — Thu, 11 Aug 2016 22:43:34 GMT

Well SAS isn't really an RDBMS (though of course it can be used like one), the SAS dataset is just a file format. People pass around files (they do not pass around RDBMS tables), and people want to be able to read the files they receive.

It looks likes IBM provides free ODBC drivers to read SPSS files, I think that's a good thing. http://www-01.ibm.com/support/docview.wss?uid=swg21483499 I'm not sure if .xlsx is fully open, but if not it was a huge step by Microsoft toward openness.

I see plenty of questions online like "How do I read [SPSS file / SAS file / Stata file /etc ] into [SQL server / Tableau / Excel/ etc ]?"

To my mind, giving the general public an easy way to read SAS datasets makes SAS more useful/valuable. If not by opening up the format, then by providing free readers. SAS already provides the SAS Universal Viewer free, which I think is great. Why not also provide free ODBC drivers to allow the public to read SAS datasets without having a SAS license? This would make SAS datasets more valuable. In the same way as the FDA's acceptance of SAS XPORT (v. 5 ! : ) files makes SAS more valuable. [FDA is able to accept them, because the the format is open (https://support.sas.com/techsup/technote/ts140_2.pdf). ]

As a side note, I'm curious how it works now for folks who develop Stat/Transfer, DataDirect drivers (which are used by many SAS/ACCESS products), and such. When SAS comes up with a new data format, do they sit down and try to reverse-engineer it so that they can read the data (same as the R folks and python folks would do?) Or does SAS partner with some of them to make it easier for them to read in SAS data correctly?

Re: Load SAS Dataset to MS SQL Server via SQL server job

Ksharp — Fri, 12 Aug 2016 04:38:57 GMT

SAS did offer the JDBC driver to allow to read SAS table by writing some Java code. 
But that is beyond my knowledge. Maybe you could give a talk to sas support and 
see what you could do. I believe even though you could read SAS table,but you 
need to write some Java Or C# code. I don't know .

Re: Load SAS Dataset to MS SQL Server via SQL server job

ACBradley — Tue, 13 Feb 2018 20:01:58 GMT

If the goal is to have MS SQL Server load the source data (originally in SAS7BDAT format) the user or admin would need to convert the SAS7BDAT file format to a supported format, such as ASCII CSV for MS SQL Server Transact-SQL or BULK INSERT to load to a DBMS table. Conversely, SAS/ACCESS Interface offers ODBC based bulk loading into MS SQL Server.

Ideally you use SAS to push to SQL Server. But if you can't, these are the standard drivers that you can use to pull SAS data in.

ODBC, using the SAS ODBC driverto access data available in SAS/SHARE or SPDS
OLE DB (Microsoft applications), using the SAS Local OLE DB Provider(for data set files like sas7bdat) or the SAS IOM OLE DB Provider (for connecting to SAS Workspace sessions)
JDBC for Java-based applications, connecting to SAS/SHARE or SAS Workspace sessions

You can also use Python -- via SASPy -- to read SAS data sets from an active SAS session and convert into Python pandas data frames.

Re: Load SAS Dataset to MS SQL Server via SQL server job

JBailey — Fri, 16 Feb 2018 18:59:15 GMT

Hey @Quentin,

Since this question is about Microsoft SQL Server, I think @ACBradley has your solution. Have your DBA check-out the SAS Local OLE DB Provider link (which points to a great article by @ChrisHemedinger).

Best wishes,

Jeff