ODS Excel for multiple sheet in one excel file with ODS excel & text in PROC Report

[raja777pharma]

Hello,

I have search many questions in this community , but i couldn't find the solution for my problem

have to  generate one excel file with multiple sheet include title in every sheet,

I need to generate by using only ODS excel with proc report , but with below code i am getting last sheet only

data sheet1;
 set sashelp.class;
 if sex eq 'M';
run;

data sheet2;
 set sashelp.class;
 if sex eq 'F';
run;


ods excel  file="%sysfunc(pathname(out))/Gender Listings.xlsx" 
 options(sheet_name="sheet1" sheet_interval="none");
 proc odstext; 
   p "This is Male data" 
     / style={background=yellow tagattr="mergeacross:6"}; run;
run;
proc Report data=sheet1 split='^';
  column Name Sex Height Age Weight ;
  define col1 /'Name'                                                               display;
  define col2 / 'Sex'                                      display;
  define col3 / 'Age' display;   
  define col4 / 'Height'                    display;
  define col5 / 'Weight'                      display;

run;
ods excel close;
ods _all_ close;
ods listing;


ods excel  file="%sysfunc(pathname(out))/Gender Listings.xlsx" 
options(sheet_name="sheet2" sheet_interval="none");
 proc odstext; 
   p "This is Female data" 
     / style={background=yellow tagattr="mergeacross:6"}; run;
run;
proc Report data=sheet2 split='^';
  column Name Sex Height Age Weight ;
  define col1 /'Name'                                                               display;
  define col2 / 'Sex'                                      display;
  define col3 / 'Age' display;   
  define col4 / 'Height'                    display;
  define col5 / 'Weight'                      display;

run;
ods excel close;
ods _all_ close;
ods listing;

when i remove ods clear between the code for generate sheet i am getting below error.

I need below output excel two sheets in one excel file ,

[Kurt_Bremser · Accepted solution]

Change your ODS statements like this:

ods excel
  file="%sysfunc(pathname(out))/Gender Listings.xlsx" 
  options(sheet_name="sheet1" sheet_interval="none")
;

proc odstext; 
   p "This is Male data" 
     / style={background=yellow tagattr="mergeacross:6"}; run;
run;

proc Report data=sheet1 split='^';
  column Name Sex Height Age Weight ;
  define col1 / 'Name' display;
  define col2 / 'Sex' display;
  define col3 / 'Age' display;   
  define col4 / 'Height' display;
  define col5 / 'Weight' display;
run;

ods excel
  options(sheet_name="sheet2")
;

proc odstext; 
   p "This is Female data" 
     / style={background=yellow tagattr="mergeacross:6"}; run;
run;

proc Report data=sheet2 split='^';
  column Name Sex Height Age Weight ;
  define col1 / 'Name' display;
  define col2 / 'Sex' display;
  define col3 / 'Age' display;   
  define col4 / 'Height' display;
  define col5 / 'Weight' display;
run;

ods excel close;