Send multiple SAS data sets to one Access Database / Excel Workbook in SAS Enterprise Guide

[Marcus_Peters]

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

[RW9 · Accepted solution]

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]

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]

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]

Thanks, that's a good clear response! I will talk to the technical architecture guys and see if they can help me out.

[Damo]

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