To export multiple SAS datasets to single excel spreedsheet using UNIX SAS9

[deleted_user]

I want to export around 50 datasets to one excel sheet and each dataset should be exported to a new worksheet within the same excel sheet.In the end it should be one single excel sheet with 50 worksheets.
But probs is I want this to be done using UNIX sas9. Can anyone help?

Thanks in advance for any help.

[Cynthia_sas]

Hi:
If you have SAS/Access for PC File Formats, you could use the LIBNAME engine to access/create the workbook:
http://support.sas.com/documentation/cdl/en/acpcref/59619/HTML/default/a002644677.htm#a002657063
(see example 2)

Or, you can use TAGSETS.EXCELXP to create a Spreadsheet Markup Language file, which is the type of XML file used by Excel 2002 and higher to define a workbook. After the file is created, you would have to FTP the file to a location where you could open the file with Excel. The file created by TAGSETS.EXCELXP is an ASCII text file. (the result of the doc='Help' is a list of allowed "suboptions" will be shown in the SAS Log)
[pre]
ods tagsets.excelxp file='multsheet.xml'
options(doc='Help');
proc print data=sashelp.class(obs=5);
run;

proc print data=sashelp.shoes(obs=5);
run;

proc print data=sashelp.prdsale(obs=5);
run;

proc print data=sashelp.heart(obs=5);
run;
ods tagsets.excelxp close;
[/pre]

This code would create the XML Spreadsheet Markup definition for a single workbook with 4 worksheets -- one for every PROC PRINT.

cynthia