Help using Base SAS procedures

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

Reply
Contributor
Posts: 27

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

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?

Super Contributor
Posts: 1,636

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

Hi,

Have you tried to import directly from access to sas?

Respected Advisor
Posts: 4,919

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

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

PG
Contributor
Posts: 27

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

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.

Respected Advisor
Posts: 4,919

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

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

PG
Respected Advisor
Posts: 2,655

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

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

Contributor
Posts: 27

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

Posted in reply to SteveDenham

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.

Respected Advisor
Posts: 2,655

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

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

Contributor
Posts: 27

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

Posted in reply to SteveDenham

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;

Respected Advisor
Posts: 2,655

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

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

Contributor
Posts: 27

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

Posted in reply to SteveDenham

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

Respected Advisor
Posts: 2,655

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

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

Super User
Posts: 19,770

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

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

Super User
Posts: 19,770

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

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;

Respected Advisor
Posts: 2,655

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

Hmm.  So that means that our Office 2010 version is backward compatible, and just keeps on using, and updating an Access 2003 database.

I tried to save our *.mdb, using Save As, and the *.accdb option didn't even appear.  I guess that you have to export from Access to get an updated db.

Steve Denham

Ask a Question
Discussion stats
  • 22 replies
  • 15995 views
  • 0 likes
  • 7 in conversation