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.
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.
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.
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 18.104.22.168-microsoft-standard-WSL2 #1 SMP Fri Jan 27 02:56:13 UTC 2023 x86_64 Red Hat Enterprise Linux
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.
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.
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.
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.
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.