DATA Step, Macro, Functions and more

Exporting Multiple SAS datasets to Access Database

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

Exporting Multiple SAS datasets to Access Database

Hi All,

     In a directory C:\MyData, reside about 30 SAS datasets. i would like to export them as tables to one big access database.

any suggestions on how to do this efficiently?

thanks in advance SAS community!


Accepted Solutions
Solution
‎03-22-2012 08:21 PM
Super User
Super User
Posts: 6,498

Exporting Multiple SAS datasets to Access Database

Try using PROC COPY.

libname in 'c:\mydata';

libname out access 'c:\mydata\mydata.accdb';

proc copy in=in out=out;

run;

View solution in original post


All Replies
Super User
Posts: 3,101

Exporting Multiple SAS datasets to Access Database

If you have SAS/ACCESS Interface to PC Files licenced then I suggest you check out PROC EXPORT.

If you don't have this product then you can still export to CSV files then use MS ACCESS to import them.

Search on MS ACCESS and PROC EXPORT on SAS support to check it further.

Solution
‎03-22-2012 08:21 PM
Super User
Super User
Posts: 6,498

Exporting Multiple SAS datasets to Access Database

Try using PROC COPY.

libname in 'c:\mydata';

libname out access 'c:\mydata\mydata.accdb';

proc copy in=in out=out;

run;

Super User
Posts: 9,671

Exporting Multiple SAS datasets to Access Database

Like Tom's:

libname in v9 'c:\mydata';

libname out  oracle path=xx user=xx password=xx ;

proc copy in=in out=out;

run;

Contributor
Posts: 62

Exporting Multiple SAS datasets to Access Database

Thanks everyone for your input. I'm running this on a 64 bit SAS Server running 9.2 so my slightly tweaked code looks like:

libname in 'c:\mydata';

libname out pcfiles server="SASSERVER.xxxxx.com" port=8621 path= 'c:\mydata\mydata.accdb';

proc copy in=in out=out;

run;

for some reason i'm not to get the access engine to load even when i specify it as accesscs. same goes for excel, xls, and excelcs.

Frequent Contributor
Posts: 101

Re: Exporting Multiple SAS datasets to Access Database

Any messages in the log?

Contributor
Posts: 62

Re: Exporting Multiple SAS datasets to Access Database

331  libname in excelcs 'C:\mydata';

ERROR: The EXCELCS engine cannot be found.

ERROR: Error in the LIBNAME statement.

Super User
Super User
Posts: 6,498

Re: Exporting Multiple SAS datasets to Access Database

What are you trying to do?

If you are moving Excel files into Acess then just use Microsoft tools to do that.

If you are having trouble getting SAS on your Server to create a libname for an Access database then check that you have the proper SAS/Access licenses AND that you are running the proper versions of SAS and Access. THere are many threads around about 64 bit SAS not working with 32 bit Microsoft drivers and the reverse.

Super User
Posts: 9,671

Re: Exporting Multiple SAS datasets to Access Database

64 bit SAS does not support Microsoft EXCEL ACCESS engine .

So at 64 bit environment, the only way to make a excel file  is to use PCFILE server just like OP did .

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 1527 views
  • 0 likes
  • 5 in conversation