Naming Excel Worksheets with BY Group Values

Many SAS users know about the ODS TAGSETS.EXCELXP destination used to create Excel Workbooks from SAS output. It is sometimes a requirement to generate one Worksheet per BY group of data processed in SAS.

The following example shows how to create one report for each value found in the variable SEX is used to create one report for each value

ods listing close;

ods tagsets.excelxp file="stocks.xml"

style=plateau

options(sheet_name='#byval1'

suppress_bylines='yes'

embedded_titles='yes'

);

proc sort data=sashelp.stocks out=stocks;

by stock;

run;

title1 "The Stock Table";

title2 "From the SASHELP Library";

proc report data=stocks nofs missing;

by stock;

column date--adjclose;

define date / display order order=internal;

run;

title;

ods _all_ close;

ods listing;

The keyword #byval1 informs SAS to place the value from the first BY variable as the sheet name. A new sheet will be created for each value in the BY variable. Note the #byval and #byvar keywords can be used whenever SAS steps are by-group processing, not just with ExcelXP tagsets. I encourage your reading on these areas.

The option suppress_bylines='yes' stops SAS writing the current BY group value as a title. As the option embedded_titles='yes' requests that SAS writes any titles into each sheet in the workbook.