How can I import a SAS dataset to Access?

[sustagens]

Hi, I have a SAS dataset I would like to import into Microsoft Access.

This is the first time I am setting up a link to SAS from outside and I'm not sure how to go about doing it

[ballardw]

Import or link?

Old school and robust is to Export the SAS data set to a CSV file (there is a wizard that should do this) and use the Access import on that file. If the SAS data set is static this may be the easiest for someone with little SAS experience.

If the SAS data will change and you want to link the question might be whether you are comfortable with ODBC to set up an ODBC link.

[sustagens]

Hi ballardw, thanks for your reply. I am currently importing it as CSV as you described and want to optimise by skipping this step.
The SAS data set will change, yes. How can I link it via ODBC? It looks for a connection definition file.

[SASKiwi]

If you have the SAS product SAS/ACCESS to PC FILES then you can use PROC EXPORT to write a SAS dataset to an ACCESS table: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=acpcref&docsetTarget=p1d0...

[sustagens]

Hi SASKiwi, thanks for your reply, unfortunately my SAS is a client/server install, and the dataset will be refreshed by a stored procedure. As per my understanding, with my client/server setup I can't use proc export to export to a local destination. I am aware of the Copy Files task in EG but because I will use the code in a stored procedure I need a code solution, not point-and-click.. What I thought I might do is create a saved import in MS Access, which imports the SAS dataset directly.

[Kurt_Bremser]

In a client/server environment, you need to set up a SAS/SHARE server on the SAS server, and install the SAS ODBC driver on the host where MS ACCESS runs. Then define the ODBC connection to your SAS server, and use that in Access.

[sustagens]

Hi KurtBremser, thanks for your reply, I searched how I can do that. First step, check if I have SAS/SHARE. Came across this thread, which you also answered back in 2018. I don't see SAS/SHARE, PRODNUM010 nor PRODNUM035 when I run proc setinit and proc product_status. Does it mean this route is a dead end for me?

[Kurt_Bremser]

I fear, yes.

The requirements for the ODBC driver are found here.

[sustagens]

I see, OK, thanks for your time Kurt!

[SASKiwi]

What OS does your SAS server run on? You could always get the SAS server to write the Access database to a network file share which is also accessible from your PC.