Importing Access DB file in SAS but failing

Question

I am trying to run below code but failing with error 'ERROR: DBMS type ACCESS not valid for import.' Not sure what is the issue.

LIBNAME db 'C:\Extra';
PROC IMPORT DBMS=ACCESS
            OUT=WORK.Finished_IP
            DATATABLE='IP Input Data'
            REPLACE; 
            DATABASE= "db.Finished data.accdb";
   USEDATE=YES;
   SCANTIME=NO;
   DBSASLABEL=NONE;
RUN;

First off, I think your DATABASE specification is wrong (or at least something I've not seen before done that way); but that's not the error you're getting. Do you have ACCESS to PC FILES licensed? — Joe
Yes. Is there any other way to extract data from access in SAS? — user2694624
If you have it licensed it should be a fine solution. Change DATABASE= to reflect the full path to the database rather than using a libname and see if that resolves your issue. — Joe

Matt_Roberts Matt_Roberts · Accepted Answer · 2014-12-23T15:36:49

If you have the "Microsoft Access Driver (*.mdb, *.accdb)" driver installed you can use this to create your libname directly to the database file like so:

libname Test ODBC noprompt="DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};
                            DBQ=C:\local\Your_DB.accdb";

You can see if you have the driver under the ODBC Data Source Administrator found in Administration Tools in the Control Panel.

Using this method means that tables in the database can be read in like normal SAS datasets.

Importing Access DB file in SAS but failing

1 Answers