How to export sas dataset into multiple tabs in excel?

sasworld — Tue, 22 Jun 2010 23:39:03 GMT

I have a dataset containing 50 states and descriptive information for each state.
I need to create reports in excel containing 50 tabs , one for each state which contains only information for that particular state. Is it possible with DDE or ODS Excel tagset?

Thanks in advance,

Re: How to export sas dataset into multiple tabs in excel?

Cynthia_sas — Wed, 23 Jun 2010 02:18:00 GMT

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

topic How to export sas dataset into multiple tabs in excel? in ODS and Base Reporting

How to export sas dataset into multiple tabs in excel?

Re: How to export sas dataset into multiple tabs in excel?