Hello
I am using ODS excel to export multiple tables to multiple sheets in one excel file.
I want to create 3 sheets: "one" "two" "three".
In the result I get only one sheet.
What is wrong?
ods excel file="/path/Ex3.XLSX"
options(sheet_name="one" sheet_interval='none');
/*Sheet one*/
Title 'Table1';
proc print data=sashelp.class(obs=3) noobs;
run;
Title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;
ods excel options(sheet_name="two" sheet_interval='none');
/*Sheet two*/
Title 'Tables3';
proc report data=sashelp.prdsale;
column country region actual predict;
define country / group page;
define region / group;
break after country / summarize page;
run;
ods excel options(sheet_name="three" sheet_interval='none');
/*Sheet three*/
Title 'Table4';
proc print data=sashelp.heart(obs=10) noobs;
run;
ods excel close;
I found the solution.
Need to use a dummy table.
I really dont know what this code is doing but it solve the problem and get the desired result
ods excel file="/path/Ex7.XLSX"
options(sheet_name="Sh1" sheet_interval="none");
/*Sheet1-2 tables*/
Title 'Table1';
proc print data=sashelp.class(obs=3) noobs;
run;
Title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;
/*Sheet2-multiple tables created by proc report*/
/*Add dummy table */
ods excel options(sheet_interval="table");
ods exclude all;
data _null_;
file print;
put _all_;
run;
ods select all;
ods excel options(sheet_name="Sh2" sheet_interval="none");
proc report data=sashelp.prdsale;
column country region actual predict;
define country / group page;
define region / group;
break after country / summarize page;
run;
/*Sheet3-one table*/
/*Add dummy table */
ods excel options(sheet_interval="table");
ods exclude all;
data _null_;
file print;
put _all_;
run;
ods select all;
ods excel options(sheet_name="Sh3" sheet_interval="none");
proc print data=sashelp.heart(obs=10) noobs;
run;
ods excel close;
You have told it not to use multiple sheets with:
sheet_interval='none'
I run this code and still dont get expected result.
In this result each table appear in a different sheet but I want that table+table2 will be in sheet one.
Tables 3 will be in sheet two
Table4 will be in sheet 4
ods excel file="/path/Ex7.XLSX"
options(sheet_name="one" );
/*Sheet one*/
Title 'Table1';
proc print data=sashelp.class(obs=3) noobs;
run;
Title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;
ods excel options(sheet_name="two" );
/*Sheet two*/
Title 'Tables3';
proc report data=sashelp.prdsale;
column country region actual predict;
define country / group page;
define region / group;
break after country / summarize page;
run;
ods excel options(sheet_name="three" );
/*Sheet three*/
Title 'Table4';
proc print data=sashelp.heart(obs=10) noobs;
run;
ods excel close;
Have you tried setting the option at the right moment:
ods excel file="/path/Ex7.XLSX"
options(sheet_name="one" sheet_interval="none");
/*Sheet one*/
Title 'Table1';
proc print data=sashelp.class(obs=3) noobs;
run;
Title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;
ods excel options(sheet_name="two" sheet_interval="table");
/*Sheet two*/
Title 'Tables3';
proc report data=sashelp.prdsale;
column country region actual predict;
define country / group page;
define region / group;
break after country / summarize page;
run;
ods excel options(sheet_name="three" sheet_interval="table");
/*Sheet three*/
Title 'Table4';
proc print data=sashelp.heart(obs=10) noobs;
run;
ods excel close;
Thank you for your reply.
However, the result of the code that you sent is not giving the desired result.
In this code there are 3 sheets :two two2 two3 and I want to create one sheet called "two" with 3 tables (from proc report)
Joe
I found the solution.
Need to use a dummy table.
I really dont know what this code is doing but it solve the problem and get the desired result
ods excel file="/path/Ex7.XLSX"
options(sheet_name="Sh1" sheet_interval="none");
/*Sheet1-2 tables*/
Title 'Table1';
proc print data=sashelp.class(obs=3) noobs;
run;
Title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;
/*Sheet2-multiple tables created by proc report*/
/*Add dummy table */
ods excel options(sheet_interval="table");
ods exclude all;
data _null_;
file print;
put _all_;
run;
ods select all;
ods excel options(sheet_name="Sh2" sheet_interval="none");
proc report data=sashelp.prdsale;
column country region actual predict;
define country / group page;
define region / group;
break after country / summarize page;
run;
/*Sheet3-one table*/
/*Add dummy table */
ods excel options(sheet_interval="table");
ods exclude all;
data _null_;
file print;
put _all_;
run;
ods select all;
ods excel options(sheet_name="Sh3" sheet_interval="none");
proc print data=sashelp.heart(obs=10) noobs;
run;
ods excel close;
The code I presented works perfectly well:
ods excel file="&_sasws_./test.xlsx" options(sheet_name="Sh1" sheet_interval="none"); title 'Table1'; proc print data=sashelp.class(obs=3) noobs; run; title 'Table2'; proc print data=sashelp.shoes(obs=3) noobs; run; ods excel options(sheet_interval="table"); proc print data=sashelp.cars; run; ods excel close;
This will print class and shoes to sh1, and cars to sh2 - it is tested working. Your "Correct" post, does nothing further than what I present here!
Can you also Add the proc report output to another sheet and check if you get it in one sheet?
proc report data=sashelp.prdsale;
column country region actual predict;
define country / group page;
define region / group;
break after country / summarize page;
run;
When I run this code:
ods excel file="&_sasws_./test.xlsx" options(sheet_name="Sh1" sheet_interval="none"); title 'Table1'; proc print data=sashelp.class(obs=3) noobs; run; title 'Table2'; proc print data=sashelp.shoes(obs=3) noobs; run; ods excel options(sheet_interval="table"); proc print data=sashelp.cars; run; proc report data=sashelp.prdsale; column country region actual predict; define country / group page; define region / group; break after country / summarize page; run; ods excel close;
I get tabs for Sh1 1 = Class and Shoes,
Sh1 2 = Cars
SH1 3-5 = the three summarised tables from the proc report, each on their own tab as three component tables are created - as clearly programmed to do this by the statement:
break after country / summarize page;
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.