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

Hello all!

 

I want to be able to export more than 1 data set to an Access database (or as separate sheets in an Excel file) but I can't figure out how to do it! Can anyone help me?

 

Thanks

Marcus Peters

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Its generally a good idea to search for answers before posting.  There are numerous methods to get data out to both Access and Excel.  Lets take Excel first:

tagsets.excelxp - this creates XML text file which Excel can read and parse.

libname excel (SAS 9.4), this can create/access XLSX files directly

proc export - can create xls files

To CSV - text file with comma separation which Excel can also read

 

With Access you can also do:

libaname access - never used myself

proc sql pass through

proc export

etc.

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Its generally a good idea to search for answers before posting.  There are numerous methods to get data out to both Access and Excel.  Lets take Excel first:

tagsets.excelxp - this creates XML text file which Excel can read and parse.

libname excel (SAS 9.4), this can create/access XLSX files directly

proc export - can create xls files

To CSV - text file with comma separation which Excel can also read

 

With Access you can also do:

libaname access - never used myself

proc sql pass through

proc export

etc.

Marcus_Peters
Fluorite | Level 6
RW9, I can't make these work in SAS EG (already tried) as it doesn't know I have access to any shared drives being a server-based session? I need a solution for an EG workflow such as writing one of the Export routines built into that platform?
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Then your problem is that your server based session does not have access to the particular areas you need to access.  This is therefore an issue for your IT group to sort out for you so that you can access those areas, nothing to do with the type of SAS code you use to create.  It may require you to use an FTP client to transfer back and forth, and if you server session is not windows with office installed, then you would need either an open source variant - XML/CSV or PCFILES.  Either way, not sure how we can help with your infrastructure.

Marcus_Peters
Fluorite | Level 6
Thanks, that's a good clear response! I will talk to the technical architecture guys and see if they can help me out.
Damo
SAS Employee

Hi @Marcus_Peters

 

Hopefully one of those solutions will satisfy you.

 

 

For excel format, you can use for instance ODS EXCEL or the ExcelXP tagset such as:

ods excel file='c:\data\output1.xlsx';
proc print data=sashelp.class; run;
proc print data=sashelp.cars; run;
ods excel close;
 
ods tagsets.ExcelXP file='c:\data\output_tagset.xls';
proc print data=sashelp.class; run;
proc print data=sashelp.cars; run;
ods tagsets.ExcelXP close;

As previously discussed with that SAS Communities post Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook, proc export is another option (if you have the SAS/ACCESS Interface to PC Files module).

 

You can use the proc export in order to export to Microsoft Access as well.

Another alternative found on SAS Communities again is Exporting Multiple SAS datasets to Access Database.

 

 

Cheers,
Damo

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5 replies
  • 1452 views
  • 3 likes
  • 3 in conversation