How do I add multiples sheets of SAS data when creating an excel report?

[mreynaud]

Hello I've been exploring the ods excel code and I've been wanting to export an excel report that displays two data sets on two different sheets but one excel file. For the sake of this example I am using data from sashelp.shoes.

The following codes creates an excel report (I removed the file pathway where is saves) that displays on one sheet called "Detail"

ODS listing close;
ODS excel FILE="\filepathway" STYLE=htmlblue
options(embedded_titles='yes' embedded_footnotes='yes' zoom='100' sheet_interval='none' sheet_name='Detail' suppress_bylines='yes'  
orientation='landscape' fittopage="no" Pages_FitWidth = '2' Pages_FitHeight = '4' autofilter='yes' 	row_repeat='header'

 embedded_footnotes='yes' Print_Footer = 'This is a test');

proc report data=sashelp.shoes;

TITLE1 "SASHELP SHOES DATA";


COLUMN (Region Product Subsidiary Sales);


DEFINE Region/ DISPLAY 'Region';
DEFINE Product/ DISPLAY 'Product';
DEFINE Subsidiary/ DISPLAY 'Subsidiary';
DEFINE Sales/ DISPLAY 'Sales';
run;


ODS _all_ CLOSE; 

But let's say I want two sheets in the excel report, the first called "Summary" which displays the frequency of each product (which is from the code below):

proc freq data= sashelp.shoes;
tables product/out=summary_freq;
run;

How would I adjust my ODS excel code to create a single report with two sheets ("Summary" and "Detail")?

[ballardw]

You should look up what your option "Sheet_interval='none' " is doing.

That will prevent creating multiple sheets.

If you want one sheet for each procedure you would 1) use Sheet_interval='Proc' and 2) place multiple procedures that create output between the ODS Excel and ODS Excel (or _all)) close;

[Reeza · Accepted solution]

ODS listing close;
ODS excel FILE="\filepathway" STYLE=htmlblue
options(embedded_titles='yes' embedded_footnotes='yes' zoom='100' sheet_interval='proc' sheet_name='Detail' suppress_bylines='yes'  
orientation='landscape' fittopage="no" Pages_FitWidth = '2' Pages_FitHeight = '4' autofilter='yes' 	row_repeat='header'

 embedded_footnotes='yes' Print_Footer = 'This is a test');

proc report data=sashelp.shoes;

TITLE1 "SASHELP SHOES DATA";


COLUMN (Region Product Subsidiary Sales);


DEFINE Region/ DISPLAY 'Region';
DEFINE Product/ DISPLAY 'Product';
DEFINE Subsidiary/ DISPLAY 'Subsidiary';
DEFINE Sales/ DISPLAY 'Sales';
run;


ods Excel options(embedded_titles='yes' embedded_footnotes='yes' zoom='100' sheet_interval='proc' sheet_name='Summary' suppress_bylines='yes'  
orientation='landscape' fittopage="no" Pages_FitWidth = '2' Pages_FitHeight = '4' autofilter='yes' 	row_repeat='header'

 embedded_footnotes='yes' Print_Footer = 'This is a test 2');

proc freq data= sashelp.shoes;
tables product/out=summary_freq;
run;

ODS excel CLOSE;