BookmarkSubscribeRSS Feed
lsirakos
Calcite | Level 5

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?

35 REPLIES 35
Linlin
Lapis Lazuli | Level 10

Hi,

Have you tried to import directly from access to sas?

PGStats
Opal | Level 21

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
lsirakos
Calcite | Level 5

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
Opal | Level 21

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

PG
SteveDenham
Jade | Level 19

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
Calcite | Level 5

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
Jade | Level 19

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
Calcite | Level 5

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
Jade | Level 19

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
Calcite | Level 5

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

SteveDenham
Jade | Level 19

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
Super User

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

Reeza
Super User

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
Tourmaline | Level 20

@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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 35 replies
  • 30192 views
  • 4 likes
  • 9 in conversation