Exporting SAS dataset to MS-Access

[shellp55]

Hello,

I've read other topics about this but what component in SAS studio should I be looking for to ensure I have the one to export to MS-Access.

My code is below and originally, I got an error message about not having the correct server but now I get the error message "ERROR: DBMS type ACCESS not valid for export".

PROC EXPORT DATA = Main
OUTTABLE='Main'
DBMS=ACCESS REPLACE;
DATABASE= "\\sas-analytics-pro\SunshineCo\DAD.ACCDB";
RUN;

How can I export directly to MS-Access as a table?   Any assistance greatly appreciated.

Shelley

[SASKiwi]

You need the SAS product SAS/ACCESS to PC File Formats installed and licensed to export to MS Access. You can check this by running this code:

proc product_status;
run;

proc setinit;
run;

Also if your SAS sessions run on Unix you will need to use the ACCESSCS engine (not ACCESS) and have the SAS PC Files Server running.

[shellp55]

Thank you! I have that component so now I am very confused as to why my code doesn't work. I found that if I added CS to the DBMS i.e. AccessCS then that is when I get the error message "ERROR: Server Name is invalid or missing.".

[Patrick]

SAS Studio is your SAS client that lets you execute code on a SAS server. Your SAS server needs access to your MS Access database. 

First thing to test is the access to the folder. Does below work?

libname test '\\sas-analytics-pro\SunshineCo';

If above works: Under which OS does your SAS server execute? Windows or Unix/Linux? 

Run below and share the output from the SAS log with us.

%put &=SYSHOSTINFOLONG;

[shellp55]

Hello,

Thank you so much for responding.   Sorry, the libname for test that actually worked was: 

libname test '/data/Sunshine/Excel';

Below is the result when I ran the code you provided: 

SYSHOSTINFOLONG=Linux LIN X64 5.15.90.1-microsoft-standard-WSL2 #1 SMP Fri Jan 27 02:56:13 UTC 2023 x86_64 Red Hat Enterprise Linux

release 8.8 (Ootpa)

[Patrick]

Your SAS executes under Linux where there aren't any libraries/dll's that would allow for a direct connection to MS Access. You need for this reason a SAS PC File server installed and configured on the Windows side. SAS 9.4 PC Files Server: Installation and Configuration Guide

SAS PC Files Server is an application that receives client requests to access data files that are specific to Microsoft Office, such as Microsoft Excel and Microsoft Access.

In below picture copied from here your SAS session is the client. 

It's a bit confusing that your path ends with folder Excel and not Access but I guess that's just some sub-optimal naming and doesn't mean that you're actually dealing with .xlsx which could get accessed directly.

[shellp55]

Thank you so much for this!  And you are correct, the "Excel" name was an incorrectly named folder that I"m using for testing.  

[shellp55]

Hello,

Sorry for all the questions but I commented that I had the correct component for PC Files.  And the document you linked to was for SAS 9.42 and I am using SAS via Viya/SAS studio.  So is your response still able to explain the issues I'm having?   How would my code change if I had a server for the Access database?  

Thanks so much.

Shelley

[SASKiwi]

What version of Viya are you using? Viya 4 which comes in monthly versions or the earlier Viya 3? It appears that the SAS PC Files Server is also used in SAS Viya and uses the PCFILES LIBNAME engine. I suggest it would be best to discuss your requirements with your SAS administrator as setting up the PC Files Server is really an admin task.

[shellp55]

I assume the most recent version of Viya since it was only recently installed.  I don't have a lot of support with setting this up so I am trying to do it myself and obviously don't have the knowledge to do so.  

Thanks for trying.

[SASKiwi]

Without knowing your Viya architecture, it is most unlikely you will be able to set this up yourself. There will most likely need to be Viya server changes made to communicate with the SAS PC Files Server application. BTW this needs to be installed on a Windows computer. That could even be your PC if you are the only one using it, but more typically it would be on a Windows server accessible from your SAS Viya linux compute server. Also the Access files to be read or created must be accessible from the computer SAS PC Files Server is installed on.  

[Kurt_Bremser]

The most simple way to transport data is the use of text files. Export from SAS with a DATA step or PROC EXPORT to a format readable by Access, copy the file to where Access runs, and import it there.

Then there's no need to set up a dedicated PC Files Server.

[shellp55]

Thank you, Kurt, but I have ++ tables to export to Access and it really would be better if I could export directly.