Importing data from Microsoft Access DB (2007/2010) with SAS 9.2

[lsirakos]

I have an Access database that gets updated daily and I was hoping to import that data into SAS so I could perform analysis on it.  I have windows SAS 9.2 but cannot figure a good way to get that data into SAS in an automated fashion.  The only way I know I could do it so it would work would be to export everything to Excel and then save as an xls file.  That would be way too time consuming given it is a daily update.  I have googled with no avail, does anyone have a solution to this?

[Linlin]

Hi，

Have you tried to import directly from access to sas?

[PGStats]

It's very simple if you have the license to SAS/ACCESS, you simply treat your database as a SAS library, you can then access every table as a SAS dataset :

libname ac Access "c:\myDir\myDatabase.accdb";

proc print data=ac.myTable; run;

PG

[lsirakos]

PGStats wrote:

It's very simple if you have the license to SAS/ACCESS, you simply treat your database as a SAS library, you can then access every table as a SAS dataset :

libname ac Access "c:\myDir\myDatabase.accdb";
proc print data=ac.myTable; run;

PG

When I try that I get the following error:

ERROR: Connect: Unrecognized database format

       'C:\path\DB.accdb'.

ERROR: Error in the LIBNAME statement.

[PGStats]

I only have MS-Access 2007. It could be that MS-Access 2010 is not supported by your version of SAS. - PG

[SteveDenham]

The Proc Import method I referred to above works with MS Access 2010 (well, at least it did the day before yesterday), but was originally written for MS Access 2007.  It is slow, slow, slow, however, taking 0.53 sec CPU time for a table with 8207 obs and 29 variables, and 1.99 sec CPU time for a table with 9598 obs and 20 variables, on a 64bit Windows machine.  I can't imagine trying to scale this to 100K obs in anything resembling a reasonable time.

Steve Denham

[lsirakos]

SteveDenham wrote:

The Proc Import method I referred to above works with MS Access 2010 (well, at least it did the day before yesterday), but was originally written for MS Access 2007.  It is slow, slow, slow, however, taking 0.53 sec CPU time for a table with 8207 obs and 29 variables, and 1.99 sec CPU time for a table with 9598 obs and 20 variables, on a 64bit Windows machine.  I can't imagine trying to scale this to 100K obs in anything resembling a reasonable time.

Steve Denham

I still get the error below:

ERROR: Connect: Unrecognized database format 'c:\path\test.mdb'.

ERROR: Error in the LIBNAME statement.

Connection Failed.  See log for details.

[SteveDenham]

Can you share the code?  I had no LIBNAME statement in my code, so I don't see why that particular error would show up.  The problem could be in the dbms= option.  Try changing from dbms = access replace; to dbms = access97 replace;.  That's the code in our older versions, and may be what is needed to recognize the database format.

Steve Denham

[lsirakos]

SteveDenham wrote:

Can you share the code?  I had no LIBNAME statement in my code, so I don't see why that particular error would show up.  The problem could be in the dbms= option.  Try changing from dbms = access replace; to dbms = access97 replace;.  That's the code in our older versions, and may be what is needed to recognize the database format.

Steve Denham

Sure, code is below, I used what you had, just changed the paths and names mainly.  I wasn't using libname either so not sure why it is throwing that error.  I also tried access97 with no avail.

PROC IMPORT OUT= WORK.NewTable

            DATATABLE= "MyTableName"

            DBMS=ACCESS REPLACE;

     DATABASE="C:\Path\test.mdb";

     SCANMEMO=YES;

     USEDATE=NO;

     SCANTIME=YES;

RUN;

[SteveDenham]

That is really odd.  I just had the Data Import Wizard open an Access database, and that is exactly the code that was written.  Have you tried using the Import Wizard in an interactive session (File-->Import Data, and then following the prompts and pull-down menus to get at Microsoft Access), and saving the code generated?

Steve Denham

[lsirakos]

That doesn't work either, I believe the issue is the database isn't Access 2003.

[SteveDenham]

I must be missing something then.  The Import Wizard should work with Access 2003 and all subsequent versions--I am running Access 2010.  Now if it is an earlier version (Office 97), then the dbms=access97 would come in.  Can you open the whole database in Access?  If so, then can you do a Save As, and get the db in that form.  Not real handy for a daily update, though.

Guess that is about as much as I can offer on this.  I hope some others have some ideas.

Steve Denham

[Reeza]

Access 2007-2010 have names that end in .accdb not .mdb

[Reeza]

The libname access on SAS 9.2 installed works fine for me, but so does a direct import.

Is the following correct:

1. You can't import the file using File>Import>Access20XX?

2. The libname method doesn't work either?

If so you're best off working with Tech Support to get help.

libname mydb access 'Database_Test.accdb';

data test;

set Mydb.Test;

run;

libname test;

[novinosrin]

@Reeza  Looking for some help and leads if you can for me to access and play with MS Access database files like dealing with SAS datasets. I do have a requirement and I've no clue how the libname works, what kind of info do i need to get the libname access engine working, what kind of license info, version etc. 

I just read the SAS doc theory for the most part. I'm gonna try that tomorrow when I get to work. My objective is not to use proc import/export and would want to have better control with libname(hopefully) or proc sql pass through. Some sample codes that connects to the DB is what i'm looking for. Thank you