Try SHEET_INTERVAL = "NOW" to start a new sheet.
This was broken in earlier versions of ODS EXCEL, I believe 9.4M5+ is where this would work correctly. If you're on an earlier version there are workarounds though.
ods excel file="/reports/test.xlsx" options(sheet_name="Sh1" sheet_interval="none");
proc print data=sashelp.class(obs=3) noobs;
run;
proc print data=sashelp.shoes(obs=3) noobs;
run;
ods excel options(sheet_name="Sh2" sheet_interval="table");
proc print data=sashelp.cars (obs=5)noobs;
run;
proc print data=sashelp.shoes(obs=5) noobs;
run;
ods excel options(sheet_name="Sh3" sheet_interval="table");
proc print data=sashelp.cars (obs=1)noobs;
run;
proc print data=sashelp.shoes(obs=1) noobs;
run;
ods excel close;
Check your version with the following code and check the log.
proc product_status;run;
@ss171 wrote:
I want 3 sheets with 2 tables each in test.xls. Can anyone help in modifying the below code
ods excel file="/reports/test.xlsx" options(sheet_name="Sh1" sheet_interval="none");
proc print data=sashelp.class(obs=3) noobs;
run;
proc print data=sashelp.shoes(obs=3) noobs;
run;
ods excel options(sheet_name="Sh2" sheet_interval="table");
proc print data=sashelp.cars (obs=5)noobs;
run;
proc print data=sashelp.shoes(obs=5) noobs;
run;
ods excel options(sheet_name="Sh3" sheet_interval="table");
proc print data=sashelp.cars (obs=1)noobs;
run;
proc print data=sashelp.shoes(obs=1) noobs;
run;
ods excel close;
