How do I import multiple MS Access tables into SAS EG? I don't have SAS/ACCESS Engine available.

[Mdxolly]

I need to import a MS Access database that has 60 tables with varying field names and lengths. How can I import all 60 tables into separate datasets into SAS EG? As the title states, I don't have SAS/ACCESS engine installed.

[SASKiwi]

Are you referring to SAS/ACCESS to PC Files? If you don't have this do you have SAS/ACCESS to ODBC? Failing both of those exporting and importing CSV files would be one of the few options left and a very labour-intensive and tedious one at that.

[Mdxolly]

Yes, SAS/ACCESS to PC Files. How do I check if I have SAS/ACCESS to ODBC in SAS EG? 

When I run this:

libname MYDB access "path/access_db_name.accdb";

I get this:

ERROR: The ACCESS engine cannot be found.
ERROR: Error in the LIBNAME statement.

[ballardw]

See if this works:

libname MYDB pcfiles path= "path/access_db_name.accdb" type=Access;

If this works you should at least see some data sets in the library but may have some issues around names of tables and variables.

You may need to place

options validvarname=any validmemname=any;

before the libname to deal with tables or variable with names like "This name has blanks" or "This has #special characters".

[Mdxolly]

I'm getting the following errors when I try this:

32 options validvarname=any validmemname=any;
____________
14

ERROR 14-12: Invalid option value any for SAS option VALIDMEMNAME.

33 libname MYDB pcfiles path= "path/access_db_name.accdb" type=Access;
ERROR: The SERVER= option is required for establishing a connection through the SAS/ACCESS Interface to PC Files.
ERROR: Error in the LIBNAME statement.

I didn't actually put "path/access_db_name.accdb" but had the filepath to my MS Access database.

[SASKiwi]

Run the SETINIT and PRODUCT_STATUS procedures to see what SAS products are licensed and installed respectively.

proc setinit;
run;

proc product_status;
run;

[Mdxolly]

Thank you. So it appears I do have interface to PC Files and ODBC available.

---SAS/ACCESS Interface to PC Files 31OCT2020 (CPU A)
---SAS/ACCESS Interface to ODBC 31OCT2020 (CPU A)

BUT....

when I run the following, I get an error:

26 libname MYLIBREF access "path/access_db_name.accdb";
ERROR: The ACCESS engine cannot be found.
ERROR: Error in the LIBNAME statement.

[SASKiwi · Accepted solution]

What OS does your SAS server run on? If the answer is Unix, then I think you are going to need the SAS PC Files Server installed and running either on your PC or on a remote Windows server. I suspect the ACCESS engine will only run on Windows. This will then allow you to use the PCFILES engine as you tried in an earlier test. You can download the software here: https://support.sas.com/downloads/browse.htm?fil=&cat=63

This link explains more: https://documentation.sas.com/?docsetId=acpcref&docsetTarget=p1ejcl3x1j6su3n1r6ihin8y36mv.htm&docset...

If you aren't familiar with any of this, then I'd suggest you open a Tech Support track and get them to walk you through what is required. It will be a whole lot quicker that way as they are the experts in this area.

[DWilson]

If your IT department is slow to set things up and assuming you can read the access files from your PC and are somewhat comfortable with R, you can:

1) install R on your PC

2) install the R RODBC package

3) Write R code to read the access files and save to a format that SAS can read (try Haven package write_SAS() or write.foreign which creates .CSV files and the SAS code to read them: https://www.rdocumentation.org/packages/foreign/versions/0.8-74/topics/write.foreign)

If you have SAS installed on a server and you happen to have R installed on the same server, you could see if you can get the RODBC package installed and then in step 3 you could write SAS IML code to call R, read the access data with the RODBC package, and then have IML copy the R object with your access data to a SAS data set; obviating the need to create text files and such.

PCFiles would probably be simpler to do. I only suggest the above if time is essential.

We ended up purchasing a 3rd party ODBC driver to allow us to read and write access using SAS proc SQL. I wish SAS would provide their own native support for MS access like they do with MS excel (sort of.)