BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Treeva
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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

15 REPLIES 15
Reeza
Super User

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

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

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

Thanks Reeza,

 

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

 

 

 

Reeza
Super User

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

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
Meteorite | Level 14

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

Thanks,
Suryakiran
Reeza
Super User
@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
Meteorite | Level 14

@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
Reeza
Super User
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
Obsidian | Level 7
Hi Reeza, you must be having PCFILES installed on your PC right ? .. the 'out' in your libname is just a library name right ?
Reeza
Super User

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

Thanks so much, Reeza

 

I tried this out and it worked! 🙂

 

Appreciated.

 

 

Best

Treeva

 

 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 15 replies
  • 3982 views
  • 1 like
  • 4 in conversation