topic Re: Importing all access database tables in SAS Programming

Importing all access database tables

Treeva — Mon, 09 Apr 2018 20:17:12 GMT

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.

Re: Importing all access database tables

Reeza — Mon, 09 Apr 2018 20:19:23 GMT

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.

Re: Importing all access database tables

Reeza — Mon, 09 Apr 2018 20:20:33 GMT

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.

Re: Importing all access database tables

Treeva — Mon, 09 Apr 2018 20:25:31 GMT

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!

Re: Importing all access database tables

Treeva — Mon, 09 Apr 2018 20:36:21 GMT

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.

Re: Importing all access database tables

Reeza — Mon, 09 Apr 2018 20:38:05 GMT

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";

Re: Importing all access database tables

SuryaKiran — Mon, 09 Apr 2018 20:40:18 GMT

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

Re: Importing all access database tables

Reeza — Mon, 09 Apr 2018 20:41:54 GMT

@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.

Re: Importing all access database tables

Treeva — Mon, 09 Apr 2018 20:45:12 GMT

Thanks Reeza,

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

Re: Importing all access database tables

Reeza — Mon, 09 Apr 2018 20:52:22 GMT

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;

Re: Importing all access database tables

SuryaKiran — Mon, 09 Apr 2018 21:00:08 GMT

@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.

Re: Importing all access database tables

Reeza — Mon, 09 Apr 2018 21:14:17 GMT

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

Re: Importing all access database tables

nayakig — Mon, 09 Apr 2018 21:34:31 GMT

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

Re: Importing all access database tables

Reeza — Mon, 09 Apr 2018 22:00:31 GMT

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';

Re: Importing all access database tables

Treeva — Fri, 13 Apr 2018 14:35:52 GMT

Thanks so much Suryakiran,

The paper has valuable information though it is somehow outdated!

Appreciated.

Re: Importing all access database tables

Treeva — Fri, 13 Apr 2018 18:23:54 GMT

Thanks so much, Reeza

I tried this out and it worked! 🙂

Appreciated.

Best

Treeva