Creating Output Files with ODS for Use by Excel - Exchanging Data Between SAS and Microsoft Excel - page 141

Example 8.16.8 Splitting One Report onto Multiple Excel Worksheets
This example uses the SHEET_NAME option with a BY statement. It causes the string specified to be used
as a prefix for the sheet names. A numeric value is added to the prefix to generate the sheet names by
ExcelXP, as shown below. This output report splits one PROC PRINT report into several worksheets.

Table 8.15: Options Used in Example 8.16.8.

ExcelXP Option Setting in Example Result

SHEET_NAME 'One Regions' Create a worksheet with the specified

name. When more than one worksheet is
output, unique worksheet names are
generated by adding a number on the end
of the sheet name, up to 32 characters.

Figure 8.43: Code to Create an Excel Workbook with Multiple Worksheets and User-
Supplied Prefixes.
ODS TAGSETS.EXCELXP BODY='c:\temp\ExcelXP\shoes8.xml'
OPTIONS(SHEET_NAME='One Region');
PROC PRINT DATA=sashelp.shoes;
BY region;
RUN;
ODS TAGSETS.EXCELXP CLOSE;

Figure 8.44: Excel Output Using the “Sheet_Name” Prefix.

Notice that the worksheet has a “BY” variable title on the top of the worksheet identifying the data on the
worksheet.