BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
1 ACCEPTED SOLUTION

Accepted Solutions
ACBradley
SAS Employee

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. 

 

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

View solution in original post

18 REPLIES 18
LinusH
Tourmaline | Level 20
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.
Data never sleeps
Quentin
Super User

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

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
SASKiwi
PROC Star

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
Super User
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.
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
LinusH
Tourmaline | Level 20
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.
Data never sleeps
Ksharp
Super User
Can you make a SHELL or BAT file and let SQL Admin execute it ?

Quentin
Super User
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?
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Tom
Super User Tom
Super User

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

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

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
LinusH
Tourmaline | Level 20
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.
Data never sleeps
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
LinusH
Tourmaline | Level 20
If you can consider transport files I think you could also accept csv files. Should be quite similar work effort.
Data never sleeps
Quentin
Super User

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

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
LinusH
Tourmaline | Level 20
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.
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 18 replies
  • 22882 views
  • 6 likes
  • 7 in conversation