topic Re: How can I import a SAS dataset to Access? in Developers

How can I import a SAS dataset to Access?

sustagens — Thu, 19 Nov 2020 02:42:20 GMT

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

Re: How can I import a SAS dataset to Access?

ballardw — Thu, 19 Nov 2020 03:59:15 GMT

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.

Re: How can I import a SAS dataset to Access?

sustagens — Thu, 19 Nov 2020 05:49:06 GMT

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.

Re: How can I import a SAS dataset to Access?

SASKiwi — Thu, 19 Nov 2020 06:01:35 GMT

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=p1d0tocg3njhmfn1d4ld2covlwm0.htm&locale=en

Re: How can I import a SAS dataset to Access?

sustagens — Thu, 19 Nov 2020 06:58:20 GMT

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.

Re: How can I import a SAS dataset to Access?

Kurt_Bremser — Thu, 19 Nov 2020 07:05:11 GMT

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.

Re: How can I import a SAS dataset to Access?

sustagens — Thu, 19 Nov 2020 07:49:28 GMT

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?

Re: How can I import a SAS dataset to Access?

Kurt_Bremser — Thu, 19 Nov 2020 08:02:55 GMT

I fear, yes.

The requirements for the ODBC driver are found here.

Re: How can I import a SAS dataset to Access?

sustagens — Thu, 19 Nov 2020 08:20:44 GMT

I see, OK, thanks for your time Kurt!

Re: How can I import a SAS dataset to Access?

SASKiwi — Thu, 19 Nov 2020 19:12:41 GMT

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.