Importing all access database tables

[Treeva]

Dear Team,

I want to use SAS to analyze data from an access database and in need to import all the tables without using proc import for each single table.

Any Helpful tips of what SAS syntax to use for that, please?

Really appreciated.

[Reeza]

Assign a libname and then PROC COPY?

This copies everything into the WORK library, you may want a permanent library. I'm not 100% sure that's the correct libname format, may need modifications.

libname myDB access 'path to access file';

proc copy in=myDB out=work;
run;

---

@Treeva wrote:

Dear Team,

I want to use SAS to analyze data from an access database and in need to import all the tables without using proc import for each single table.

Any Helpful tips of what SAS syntax to use for that, please?

Really appreciated.

---

[Treeva]

Hi Reeza,

Thanks a lot for your response.

I've tried the code and it gave me the following errors:

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.

I believe that the bitnesses of SAS and Microsoft Office don't match. How can I modify your provided code then, please?

Thanks.

[Reeza]

It's the libname statement. 

Do you have PCFILES installed and running? You need that if you have different bits.

I think it's like this, but you need the proper file name and extension:

libname myDB pcfiles path=" path to access db.mcaddb";

[Treeva]

Thanks Reeza,

Yes I tried this and also not working. It is definitely the SAS PC Files server issue.

[Reeza]

Try the ODBC method outlined in the paper linked to by @SuryaKiran, assuming you have a license for ODBC driver.

You can check your version/license with the following:

proc product_status; *what is installed;
proc setinit;*what is licensed;
run;

[Reeza]

Sorry, I assumed you were using BASE SAS, not SAS DI, are you using SAS DI? If so, I'll move this post back to data management.

[Treeva]

Thanks so much Reeza,

yes I am using Base SAS, not SAS DI. I've just learned that so my apologies for not making this differentiation ahead while posting my question! 

[SuryaKiran]

Check this paper Data Transfer from Microsoft Access to SAS Made Easy 

[Reeza]

@SuryaKiran the age of the paper (2005) makes me think some of the statements in there may no longer be true, regarding what's possible in SAS today.

[SuryaKiran]

@Reeza Agree, I'm excited to test this approach with current version of SAS as well as Access. Not sure if ti's really possible or not but will give a try.

[Reeza · Accepted solution]

libname out pcfiles path="C:\_LOCALdata\Issues Web Database1.accdb"; 

proc copy in=out out=work;
run;

This worked for me, SAS 9.4 TS1M3 (64 bit), MS Office is 32 bit.

Log:

400 libname out pcfiles path="C:\_LOCALdata\Issues Web Database1.accdb";
NOTE: Libref OUT was successfully assigned as follows:
Engine: PCFILES
Physical Name: C:\_LOCALdata\Issues Web Database1.accdb

401 proc copy in=out out=work;
NOTE: Writing HTML Body file: sashtml.htm
402 run;

NOTE: Copying OUT.Comments to WORK.COMMENTS (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines.
System Option for BUFSIZE was used.
NOTE: There were 1 observations read from the data set OUT.Comments.
NOTE: The data set WORK.COMMENTS has 1 observations and 5 variables.
NOTE: Copying OUT.CommentsExtended to WORK.COMMENTSEXTENDED (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines.
System Option for BUFSIZE was used.
NOTE: There were 1 observations read from the data set
OUT.CommentsExtended.
NOTE: The data set WORK.COMMENTSEXTENDED has 1 observations and 6
variables.
NOTE: Copying OUT.Issues to WORK.ISSUES (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines.
System Option for BUFSIZE was used.
NOTE: There were 3 observations read from the data set OUT.Issues.
NOTE: The data set WORK.ISSUES has 3 observations and 15 variables.
NOTE: Copying OUT.IssuesClosed to WORK.ISSUESCLOSED (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines.
System Option for BUFSIZE was used.
NOTE: There were 0 observations read from the data set OUT.IssuesClosed.
NOTE: The data set WORK.ISSUESCLOSED has 0 observations and 18 variables.
NOTE: Copying OUT.IssuesExtended to WORK.ISSUESEXTENDED (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines.
System Option for BUFSIZE was used.
NOTE: There were 3 observations read from the data set OUT.IssuesExtended.
NOTE: The data set WORK.ISSUESEXTENDED has 3 observations and 18 variables.
NOTE: Copying OUT.IssuesOpen to WORK.ISSUESOPEN (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines.
System Option for BUFSIZE was used.
NOTE: There were 3 observations read from the data set OUT.IssuesOpen.
NOTE: The data set WORK.ISSUESOPEN has 3 observations and 18 variables.
NOTE: Copying OUT.IssuesRelated to WORK.ISSUESRELATED (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines.
System Option for BUFSIZE was used.
NOTE: There were 0 observations read from the data set OUT.IssuesRelated.
NOTE: The data set WORK.ISSUESRELATED has 0 observations and 4 variables.
NOTE: Copying OUT.RelatedIssues to WORK.RELATEDISSUES (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines.
System Option for BUFSIZE was used.
NOTE: There were 0 observations read from the data set OUT.RelatedIssues.
NOTE: The data set WORK.RELATEDISSUES has 0 observations and 3 variables.
NOTE: Copying OUT.Users to WORK.USERS (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines.
System Option for BUFSIZE was used.
NOTE: There were 2 observations read from the data set OUT.Users.
NOTE: The data set WORK.USERS has 2 observations and 4 variables.
NOTE: PROCEDURE COPY used (Total process time):
real time 1.54 seconds
cpu time 0.34 seconds

[nayakig]

Hi Reeza, you must be having PCFILES installed on your PC right ? .. the 'out' in your libname is just a library name right ?

[Reeza]

Yes, I have PCFILES, and yes OUT in the libname statement can be replaced by any valid SAS library name (8 characters, starts with letters, no spaces/specialchars except _). 

I think ODBC may be the approach you need to take, sadly a bit convoluted. 

First go to your control panel> ODBC, set up a user connection to your Access file. Then  your libname will be along the lines of:

libname out ODBC dsn=your_dsn_name;

Try this one as well first though:

libname out accessCS 'path to your access filse';

[Treeva]

Thanks so much, Reeza

I tried this out and it worked! 🙂

Appreciated.

Best

Treeva