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

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

lsirakos — Wed, 09 Jan 2013 02:50:33 GMT

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?

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

Linlin — Wed, 09 Jan 2013 03:07:22 GMT

Hi，

Have you tried to import directly from access to sas?

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

PGStats — Wed, 09 Jan 2013 03:09:02 GMT

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;

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

SASKiwi — Wed, 09 Jan 2013 03:12:04 GMT

Do you have SAS/ACCESS for PC Files licensed? Run:

PROC SETINIT NOALIAS;
RUN;

to confirm. This product allows you to import ACCESS databases directly.

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

Linlin — Wed, 09 Jan 2013 03:16:49 GMT

a paper:

http://www.lexjansen.com/pharmasug/2005/coderscorner/cc12.pdf

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

SteveDenham — Wed, 09 Jan 2013 14:38:56 GMT

Here is another method, ported from 9.2 to 9.3, using Proc Import. It also requires SAS/ACCESS for PC Files.

PROC IMPORT OUT= WORK.NewBISRT

DATATABLE= "BISNewRT"

DBMS=ACCESS REPLACE;

DATABASE="B:\BIS Report Tracking\New BIS RT.mdb";

SCANMEMO=YES;

USEDATE=NO;

SCANTIME=YES;

RUN;

Steve Denham

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

lsirakos — Wed, 09 Jan 2013 15:07:46 GMT

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;

When I try that I get the following error:

ERROR: Connect: Unrecognized database format

'C:\path\DB.accdb'.

ERROR: Error in the LIBNAME statement.

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

lsirakos — Wed, 09 Jan 2013 15:09:13 GMT

SASKiwi wrote:

Do you have SAS/ACCESS for PC Files licensed? Run:

PROC SETINIT NOALIAS;
RUN;

to confirm. This product allows you to import ACCESS databases directly.

I do have SAS/ACCESS for PC Files

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

PGStats — Wed, 09 Jan 2013 15:31:39 GMT

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

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

SteveDenham — Wed, 09 Jan 2013 15:41:21 GMT

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

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

lsirakos — Wed, 09 Jan 2013 15:49:33 GMT

SteveDenham wrote:

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.

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

SteveDenham — Wed, 09 Jan 2013 16:03:48 GMT

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

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

lsirakos — Wed, 09 Jan 2013 16:51:44 GMT

SteveDenham wrote:

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;

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

SteveDenham — Wed, 09 Jan 2013 17:16:28 GMT

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

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

lsirakos — Wed, 09 Jan 2013 17:30:03 GMT

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

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

SteveDenham — Wed, 09 Jan 2013 17:38:33 GMT

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

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

Reeza — Wed, 09 Jan 2013 18:23:44 GMT

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

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

Reeza — Wed, 09 Jan 2013 18:26:16 GMT

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;

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

SteveDenham — Wed, 09 Jan 2013 18:34:51 GMT

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

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

lsirakos — Wed, 09 Jan 2013 22:54:20 GMT

Yeah I could only save as accdb but I just changed the extension to mdb to see if that would work (it didn't obviously). I know I can only import excel files that are .xls and not .xlsx, is there a fix out there for 9.2 to account for this or is it just something I will have to deal with?