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")?
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.