export 2 table into sheet1 and 2 tables into sheet 2 in one XLSX file

[Ronein]

Hello

I wan to export 2 table into sheet1 and 2 tables into sheet 2

What is the way please?

/****I would like to have several tables on Sheet 1 and several more on Sheet 2***/
/****I would like to have several tables on Sheet 1 and several more on Sheet 2***/
/****I would like to have several tables on Sheet 1 and several more on Sheet 2***/
ods excel file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/ABC12.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_name="Sh2" sheet_interval="none");
proc print data=sashelp.cars(obs=5);
run;
title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;
ods excel close;

[LinusH]

This blog gives some examples:
https://blogs.sas.com/content/sgf/2019/04/23/ods-excel-control/

[Ronein]

I tried none and after none or none and after porc but nothing provide desire results

[svh · Accepted solution]

LinusH's post had the answer--the second 'ODS excel options' statement needs sheet_interval set to "now". 

ods excel file="ABC12.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_name="Sh2" sheet_interval="now"); /*This tells SAS to start a new sheet for next PROCs*/
proc print data=sashelp.cars(obs=5);
run;
title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;
ods excel close;

[Tom]

The value you pass with SHEET_NAME= is ignored when you tell ODS EXCEL not to make a new sheet by using SHEET_INTERVAL='NONE'.

Use SHEET_INTERVAL='NOW' instead.

ods excel options(sheet_name="Sh2" sheet_interval="now");