Hi:
If you use PROC PRINT, PROC REPORT and/or PROC TABULATE with BY group processing (or other procedures with BY groups or other types of processing, such as the PAGE dimension in TABULATE...), you can generate a sheet for every interval that you specify. With BY group processing, a new sheet for every BY group table is automatic behavior.
[pre]
proc sort data=sashelp.class out=class;
by sex name;
run;
ods listing close;
ods tagsets.excelxp file='c:\temp\bygender.xls' style=sasweb;
proc print data=class noobs;
by sex;
var name age height weight;
run;
ods tagsets.excelxp close;
[/pre]
Even if you do NOT use ODS TAGSETS.EXCELXP, you can use PROC EXPORT to create multiple sheet in one workbook using the following code (code is shown for SAS 9.1.3 -- creates an XLS file...not an XLSX file):
[pre]
** Proc Export method to create multiple sheets;
proc sort data=sashelp.shoes out=Pshoes;
where region = 'Pacific';
by region subsidiary;
run;
proc sort data=sashelp.shoes out=WEshoes;
where region = 'Western Europe';
by region subsidiary;
run;
proc sort data=sashelp.shoes out=Drshoes;
where product contains 'Dress';
by region subsidiary;
run;
proc export data=Pshoes
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="Pacific";
run;
proc export data=WEshoes
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="West_Eur";
run;
proc export data=DRshoes
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="Dressy";
run;
[/pre]
You will note that the difference in the outputs is that the TAGSETS.EXCELXP output does use the SASWEB style information; while the PROC EXPORT method results in a set of worksheets that do not have any style information.
For more information about controlling spreadsheet options and suboption with ODS TAGSETS.EXCELXP, refer to this paper:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf
cynthia
