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

Notice that the worksheet names and the top of the worksheet both have the “BY” value that describes the
variable name and its value.

Example 8.16.10 Use SHEET_INTERVAL= BYGROUP to Create Worksheets
This example uses the SHEET_INTERVAL option to build a separate worksheet at different intervals. This
ExcelXP option has several values that produce different results. See Table 8.18 for details. Here in this
example we will use the “BYGROPUP” setting and in the next example, 8.16.11, we will use the “PROC”
setting.

Table 8.17: Options Used in Example 8.16.10.
ExcelXP Option Setting in Example Result

Create a worksheet with the specified name.
When more than one worksheet is output,
the BY variable and value are used for the
sheet name, up to 32 characters.

SHEET_INTERVAL 'BYGROUP'

Table 8.18: Descriptions of Other Options Available for the SHEET_INTERVAL Option.

SHEET_INTERVAL Result

TABLE Split the outputs into new pages when a new “TABLE” starts. This is the

default.

PAGE Split the outputs into new pages when a new “PAGE” starts.

BYGROUP Split the outputs into new pages when a new “BYGROUP” starts.

PROC Split the outputs into new pages when a new “PROC” starts.

NONE Does not apply the option.

When you are using the SHEET_INTERVAL='BYGROUP' option in conjunction with the PROC PRINT
“BY” statement, each BY group is printed on a separate worksheet with the value of the BY group variable
as part of the name of the worksheet.

Figure 8.47: Code to Use “SHEET_INTERVAL” for Labeling Worksheet Names.
ODS TAGSETS.EXCELXP BODY='c:\temp\ExcelXP\shoes10.xml'
OPTIONS(SHEET_INTERVAL='BYGROUP');
PROC PRINT DATA=sashelp.shoes;
BY region;
RUN;
ODS TAGSETS.EXCELXP CLOSE;

Figure 8.48 shows the result of running the code in Figure 8.47 and causes the data for each region to be on
a separate Excel worksheet.