Usage Note 33564: Creating multiple worksheets per Microsoft Excel workbook using ODS
Beginning with SAS 9.1, you can use the ODS tagsets.ExcelXP destination to generate multiple worksheets per workbook. This ODS destination generates output in the SpreadsheetML format, which was one of Excel's new XML formats beginning with Excel 2002. This was the first release of Excel that fully supported multiple worksheets per workbook for a file format other than the binary or native Excel format. The ExcelXP destination emulates this format and enables you change parameters within the worksheet and workbook with options on the ODS statement. Each piece of output is added to a new sheet by default. As of release SAS 9.2, this ODS destination does not allow images.
Operating System and Release Information
| SAS System | Base SAS | z/OS | 9.1 TS1M0 | |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M0 | |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.1 TS1M0 | |
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.1 TS1M0 | |
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M0 | |
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M0 | |
| Microsoft Windows 2000 Server | 9.1 TS1M0 | |
| Microsoft Windows 2000 Professional | 9.1 TS1M0 | |
| Microsoft Windows NT Workstation | 9.1 TS1M0 | |
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M0 | |
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M0 | |
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M0 | |
| Microsoft Windows XP Professional | 9.1 TS1M0 | |
| 64-bit Enabled AIX | 9.1 TS1M0 | |
| 64-bit Enabled HP-UX | 9.1 TS1M0 | |
| 64-bit Enabled Solaris | 9.1 TS1M0 | |
| HP-UX IPF | 9.1 TS1M0 | |
| Linux | 9.1 TS1M0 | |
| OpenVMS Alpha | 9.1 TS1M0 | |
| Tru64 UNIX | 9.1 TS1M0 | |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
The syntax generates an XML file using the ExcelXP destination changing the sheet names from the default using the Sheet_Name= option on the ODS statement.
ods tagsets.ExcelXP file="c:\temp\temp.xml";
ods tagsets.excelxp options(sheet_name="First sheet");
proc print data=sashelp.class;
run;
ods tagsets.excelxp options(sheet_name="Second sheet");
proc print data=sashelp.air ;
run;
ods tagsets.ExcelXP close;
Using the Excelxp destination to generate multiple worksheets per workbook
| Type: | Usage Note |
| Priority: | |
| Topic: | SAS Reference ==> ODS (Output Delivery System)
Data Management ==> Data Sources ==> External Databases ==> MS Excel
|
| Date Modified: | 2008-10-17 11:48:23 |
| Date Created: | 2008-10-09 06:59:45 |
