DATA Step, Macro, Functions and more

Importing all access database tables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Importing all access database tables

[ Edited ]

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.


Accepted Solutions
Solution
‎04-13-2018 02:24 PM
Super User
Posts: 24,004

Re: Importing all access database tables

Posted in reply to SuryaKiran
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

View solution in original post


All Replies
Super User
Posts: 24,004

Re: Importing all access database tables

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.


 

Occasional Contributor
Posts: 6

Re: Importing all access database tables

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.

 

Super User
Posts: 24,004

Re: Importing all access database tables

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";
Occasional Contributor
Posts: 6

Re: Importing all access database tables

Thanks Reeza,

 

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

 

 

 

Super User
Posts: 24,004

Re: Importing all access database tables

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;
Super User
Posts: 24,004

Re: Importing all access database tables

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.
Occasional Contributor
Posts: 6

Re: Importing all access database tables

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! 

PROC Star
Posts: 629

Re: Importing all access database tables

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

Thanks,
Suryakiran
Super User
Posts: 24,004

Re: Importing all access database tables

Posted in reply to SuryaKiran
@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.
PROC Star
Posts: 629

Re: Importing all access database tables

[ Edited ]

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

Thanks,
Suryakiran
Solution
‎04-13-2018 02:24 PM
Super User
Posts: 24,004

Re: Importing all access database tables

Posted in reply to SuryaKiran
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

Occasional Contributor
Posts: 19

Re: Importing all access database tables

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

Re: Importing all access database tables

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';
Occasional Contributor
Posts: 6

Re: Importing all access database tables

Thanks so much, Reeza

 

I tried this out and it worked! Smiley Happy

 

Appreciated.

 

 

Best

Treeva

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 190 views
  • 1 like
  • 4 in conversation