How to ODS proc report multiple datasets to one excel file but with different sheets

[purpleclothlady]

Hi all:

I tried to ODS proc report multiple datasets to One excel file but with different sheets

the code is not working. any fix? 

thanks 

%macro mm2(ds);
title;
footnote;

ods results off;
ods listing close;
ods escapechar="^";

ods excel file="C:\rpt.xlsx" 
		style=excel
		options 
    (row_repeat="header"
		 frozen_headers="ON" 
		 frozen_rowheaders="OFF" 
		);

%if &ds=sashelp.cars %then %do;
ods excel options (sheet_name = "A");

proc report data=sashelp.cars nowd split='~' missing style(header)=[just=left]
	style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box]; 

	%IF &num > 0 %Then %Do;
      column make ;
			  define make	/"make";
	  run;
	%End;

	%Else %if &num=0 %then %Do;
		 proc report data = noobs;
			 column x;
			 define x / display "";
		 run;
	%End;	
%end;


 %if &ds=sashelp.class %then %do;
ods excel options(autofilter="ALL" sheet_name="C");
	proc report data=sashelp.class nowd split="~" missing
	style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box] ;
       column name ;

			    define name	 /"name" ;
		run;
	%End;
	
	%Else %if &num=0 %then %Do;
		 proc report data = noobs;
			 column x;
			 define x / display "";
		 run;
	%End;	
%end;

 %if &ds=sashelp.shoes %then %do;
ods excel options(autofilter="ALL" sheet_name="d");
	proc report data=sashelp.shoes nowd split="~" missing
	style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0]; 

   %IF &num. > 0 %Then %Do;
      column region ;
			    define region/ "region";          
		run;
	 %End;

	 %Else %Do;
		 proc report data =noobs;
			 column x;
			 define x / display "";
		 run;
	 %End;
%end;


/*%MACRO mm(ds);	*/
%mend;

%mm2(sashelp.car);
%mm2(sashelp.class);
%mm2(sashelp.shoes);

ods excel close;
ods results on;
ods listing;

[ballardw]

I think you may want to add: Sheet_interval='PROC' to your first ODS Excel options list. That should create one sheet for each procedure call as long as you do not override Sheet_interval setting.

HOWEVER: You would have to set the file outside of your macro. Currently you recreate the Excel file each time you call the macro because inside the macro you set the file name. Which overwrites the previous version each time you call the macro.

You also have a "not defined to us" macro variable &num so I have no idea how that would impact this. 

Better would be to extract just the REPORT sections of the code and have the ODS Excel with the File name option outside and then close the file apart from the macro.

This  would look more like:

%macro mm2(ds);
%if &ds=sashelp.cars %then %do;
ods excel options (sheet_name = "A");

proc report data=sashelp.cars nowd split='~' missing style(header)=[just=left]
	style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box]; 

	%IF &num > 0 %Then %Do;
      column make ;
			  define make	/"make";
	  run;
	%End;

	%Else %if &num=0 %then %Do;
		 proc report data = noobs;
			 column x;
			 define x / display "";
		 run;
	%End;	
%end;


 %if &ds=sashelp.class %then %do;
ods excel options(autofilter="ALL" sheet_name="C");
	proc report data=sashelp.class nowd split="~" missing
	style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box] ;
       column name ;

			    define name	 /"name" ;
		run;
	%End;
	
	%Else %if &num=0 %then %Do;
		 proc report data = noobs;
			 column x;
			 define x / display "";
		 run;
	%End;	
%end;

 %if &ds=sashelp.shoes %then %do;
ods excel options(autofilter="ALL" sheet_name="d");
	proc report data=sashelp.shoes nowd split="~" missing
	style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0]; 

   %IF &num. > 0 %Then %Do;
      column region ;
			    define region/ "region";          
		run;
	 %End;

	 %Else %Do;
		 proc report data =noobs;
			 column x;
			 define x / display "";
		 run;
	 %End;
%end;


%mend;/*%MACRO mm(ds);	*/


title;
footnote;

ods results off;
ods listing close;
ods escapechar="^";

ods excel file="C:\rpt.xlsx" 
		style=excel
		options 
    (row_repeat="header"
		 frozen_headers="ON" 
		 frozen_rowheaders="OFF" 
       sheet_interval="PROC"
		);

%mm2(sashelp.car);
%mm2(sashelp.class);
%mm2(sashelp.shoes);

ods excel close;
ods results on;
ods listing;

I'm not sure what you gain by stuffing data set tests and then selecting the proc report code based on those names unless you have many sets with similar structures. Generally if I have multiple sets that need similar treatment, I would make a specific to that structure report macro and then call each specific macro with an appropriate data set.

I did not test any of the code above, partially because of that &num macro variable.

[purpleclothlady · Accepted solution]

@ballardw :

you are right, I am stuck with the overwriting part. I have to took the first ods part out of the macro and manipulate the other parts of the code. 

BTW: By adding "Sheet_interval="PROC" did work nicely,

thanks all

title;
footnote;
ods results off;
ods listing close;
ods escapechar="^";
ods excel file="C:\rpt.xlsx" 
		style=excel
		options 
              (row_repeat="header"
		 /*to output to different sheets*/
                  sheet_interval="PROC");


%macro mm2(ds);
%if &ds=sashelp.cars %then %do;
ods excel options (sheet_tname = "A");
proc report data=sashelp.cars nowd split='~' missing style(header)=[just=left]
	style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box]; 
      column make ;
			  define make	/"make";
	  run;	
%end;

 %else %if &ds=sashelp.class %then %do;
ods excel options(autofilter="ALL" sheet_name="C");
	proc report data=sashelp.class nowd split="~" missing
	style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box] ;
       column name ;
			    define name	 /"name" ;
		run;
%end;
%else %if &ds=sashelp.shoes %then %do;
ods excel options(autofilter="ALL" sheet_name="d");
	proc report data=sashelp.shoes nowd split="~" missing
	style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0]; 
      column region ;
			    define region/ "region";          
		run;
%end;
/*%MACRO mm(ds);	*/
%mend;
%mm2(sashelp.cars	);
%mm2(sashelp.class	);
%mm2(sashelp.shoes	);
ods excel close;
ods results on;
ods listing;