Why the proc report overwrite the previous excel sheet when output multiple sheets on one excel file

[purpleclothlady]

hi all: 

Please advise this trivia 😊, it seems the output of excel sheet will be overwritten by the new proc report excel output.

Goal: Need to output multiple sheets to One excel file.

1. I need to first run multiple macros for different data sets (%mm) and then Output multiple final datasets (A,C) to one excel file- test.xlsx

2. &&var&i and d_&&var&i  came from the %mm , which are used to identify difference for each variables between old and new version of the data set. (this &&var&i will resolve to different value based on %mm)

3.%sqlobs is the total number of variables in each dataset (for example A: has 22 variables)

Thank you,

purple

%mm(A,  &_va1.,term,test);
/*-----------------------------------------------------------------------------------------------------*																																							Step6. Output
*-----------------------------------------------------------------------------------------------------*/	
title;
footnote;
ods results off;
ods listing close;
ods escapechar="^";

ods excel file="C:\Users\test.xlsx" 
		style=excel
		options 
    (row_repeat="header"
		 frozen_headers="ON" 
		 frozen_rowheaders="OFF" 
		 ROWBREAKS_INTERVAL= "OUTPUT"
		 sheet_label=" "
		 embedded_titles="YES"
		 FitToPage="ON"
		 orientation="landscape"
		 flow="ROWHEADERS");

%macro rpt;
%let sqlobs=22;
ods excel options (sheet_name = "A");
proc report data=A nowd split='~' missing style(header)=[just=left]
	style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box] 
	style(column)=[font=(Arial, 9pt) bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt cellwidth=1in vjust=m] 
	style(header)=[font=(Arial, 9pt) bordercolor=#f0f0f0 borderstyle=solid borderwidth=1pt font_weight=bold foreground=white background=#ADD8E6 vjust=m];

  %IF &num. > 0 %THEN %DO; 
 	 column subject recno cat %do i=1 %to &sqlobs.; &&var&i  %end; %do i=1 %to &sqlobs.; d_&&var&i %end;  ;		
			  define subject / display "Subject ID"   							style(column)=[cellwidth=0.7in];
				define recno  /display order=data noprint;
				define cat    / display  "Category ";
                                define a     / display "	"						style(column) = [just = l cellwidth = 2.2in];
				define b    / display "" 								style(column) = [just = l cellwidth = .8in];
				define c    / display "";
        	%do i=1 %to &sqlobs. ; 
        define D_&&var&i / order=data noprint;
        %end;  ;						
		 %do i=1 %to &sqlobs; 	

			compute &&var&i;
					if cat="New Record" then do;
						call define("cat", "style", "style=[background=lightgreen]");
						call define("&&var&i", "style", "style=[background=lightgreen]");
					end;
					else if cat="Deleted Record" then do;
						call define("cat", "style", "style=[background=lightred]");
						call define("&&var&i", "style", "style=[background=lightred]"); 
					end; 
			 endcomp;

			 compute D_&&var&i;
					if D_&&var&i="Yes" then do;
						call define("cat", "style", "style=[background=yellow]");
						call define("&&var&i", "style", "style=[background=yellow]");
					end;
			 endcomp;
		%end;
	run;
	%END;
	%ELSE %DO;
			 column x;
			 define x / display "";
		 run;
	%END;

%mend;
%rpt;
ods excel close;

/*-----------------c-----------------*/
%mm(C,  &_va3.,term,test);			
title;
footnote;
ods results off;
ods listing close;
ods escapechar="^";

ods excel file="C:\Users\test.xlsx" 
		style=excel
		options 
    (row_repeat="header"
		 frozen_headers="ON" 
		 frozen_rowheaders="OFF" 
		 ROWBREAKS_INTERVAL= "OUTPUT"
		 sheet_label=" "
		 embedded_titles="YES"
		 FitToPage="ON"
		 orientation="landscape"
		 flow="ROWHEADERS");

%macro rpt;
%let sqlobs=27;
ods excel options(autofilter="ALL" sheet_name="C");
	proc report data=C nowd split="~" missing
	style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box] 
	style(column)=[font=(Arial, 9pt)  bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt cellwidth=0.6in vjust=m] 
	style(header)=[font=(Arial, 9pt) background=#ADD8E6 bordercolor=#f0f0f0 borderstyle=solid borderwidth=1pt font_weight=bold foreground=black vjust=m];

   %IF &num. > 0 %THEN %DO; 
 	  column subject recno cat %do i=1 %to &sqlobs.; &&var&i  %end; %do i=1 %to &sqlobs.; D_&&var&i %end;  ;		
	
			    define subject	 / display "Subject ID"   			style(column)=[cellwidth=0.7in];
					define co      /display order=data noprint;
					define seq  / display noprint      				style(column)=[cellwidth=1.5in];
          define et   / display ""             	style(column)=[cellwidth=1.5in];
    		  define rv	 	/ display "";
				  define es		/ display "";
		
					%do i=1 %to &sqlobs. ; 
          define D_&&var&i / order=data noprint;
          %end;  
						
					 %do i=1 %to &sqlobs; 	

						compute &&var&i;
								if cat="New Record" then do;
									call define("cat", "style", "style=[background=lightgreen]");
									call define("&&var&i", "style", "style=[background=lightgreen]");
								end;
								else if cat="Deleted Record" then do;
									call define("cat", "style", "style=[background=lightred]");
									call define("&&var&i", "style", "style=[background=lightred]"); 
								end; 
						 endcomp;

						 compute D_&&var&i;
								if D_&&var&i="Yes" then do;
									call define("cat", "style", "style=[background=yellow]");
									call define("&&var&i", "style", "style=[background=yellow]");
								end;
						 endcomp;
					%end;
			run;
	 %End;

	 %Else %Do;
			 column x;
			 define x / display "";
		 run;
	 %End;
%mend;
%rpt;

ods excel close;
ods results on;
ods listing;

[Cynthia_sas · Accepted solution]

Hi:
Each time you run an ODS EXCEL "sandwich" if the FILE= value is the same as any previous time, the new report will overwrite the previous report. That is the way ODS is designed. Typically, if you want multiple sheets from multiple PROC REPORT steps in one WORKBOOK, you do something like this, conceptually:

In this case, there should be one workbook created with 4 separate sheets because each PROC REPORT step will start a new sheet by default (unless you change the default behavior with suboptions). As you can see, what is highlighted in yellow could be replaced by a Macro program loop that generated multiple PROC REPORT steps. However, if the ODS EXCEL statement for FILE= was inside the loop, you'd see the behavior you mentioned. The ODS EXCEL FILE= statement is usually placed OUTSIDE of a Macro loop. The placement of the CLOSE will depend on your program logic. Usually the ODS EXCEL CLOSE is also outside of the a Macro program loop.

Cynthia

[Tom]

You need to move the ODS EXCEL FILE= statemen to outside of the loop if you want to write multiple worksheets into the same physical file.

* starting ;
ods excel file='myfile.xlsx';

*Looping;
ods excel options (sheet_name="SHEET1");
proc ....
ods excel options (sheet_name="SHEET2");
proc ...
...

* ending;
ods excel close;

You also have seem to re-using the same macro name over and over.  If they are different then give them different name.  If they are the same then define it only once. That will reduce confusion for the poor humans that have to try to understand the code.  Define the macros first. Then start writing the executable part of the code.  So your program layout might look more like this:

%macro rpt(parameter);
...
%mend rpt;


%mm;
%rpt(firsttime);

%mm;
%rpt(secondtime);

[purpleclothlady]

Hi @Cynthia_sas @Tom :

Thanks so much for the tip. I resolved the issue. Without your expertise, I won't be able to figure out so quickly. thanks again. 

this is final flow please find in attachment.

have a wonderful day,

p

[purpleclothlady]

Hi all @Tom @Cynthia_sas  : 

I was trying to find out  if there is a way to suppress the One of the proc report 

for eg. I have 3 data sets (A, B ,C) in the macro, but I only want 2 of them (A, B) create excel. 

How may I suppress one of  dataset for proc report output? currently the  "layer O " will automatically create a sheet for B

I tried to use 

/*Layer O*/
title;
ods results off;
ods listing close;
ods escapechar="^";
ods excel file=c\want.xlsx";

/*Layer inside*/
%macro tt;
%if &ds=A %then %do;
proc report;
%end;

%if &ds=C %then %do;
proc report;
%end;

%mend;
%tt (A);
%tt(B);
%tt(C);

/*Layer O*/
ods excel close;
ods results on;
ods listing;

[Tom]

Probably the devil is in the details.

1) Your PROC step is missing the RUN statement to end it, so it will stay open until you start some other step.

2) Your macro does not take any parameters, so you cannot call it with parameters.

[Cynthia_sas]

Hi: I expect you'll have trouble with your code because you don't specify the path for the FILE= option correctly. As far as I know, on Windows, it needs to be C:\, not just C\ as the starting drive letter. Really, if you don't want the PROC REPORT for dataset C in the ODS EXCEL file, then you should only need to move your macro call OUTSIDE of the ODS EXCEL CLOSE; Assuming your PROC REPORT code is complete for the C data, then you should only need to move that single invocation outside the ODS EXCEL "sandwich".
Cynthia

[purpleclothlady]

hi @Cynthia_sas and @Tom : 

Now I fully understand the sandwich concept. have a great weekend. 😊

Here is the code and I tested. 

/*Layer O*/
title;
ods results off;
ods listing close;
ods escapechar="^";
ods excel file="C:\showABONLY.xlsx";

data A;
set sashelp.class;
keep name;
run;
data B;
set sashelp.shoes;
keep region;
run;

data C;
set sashelp.cars;
keep make;
run;

/*Layer inside*/
%macro tt(ds);
%if &ds=A %then %do;
ods excel options(autofilter="ALL" sheet_name="A");
proc report data=&ds;
column name;
define name/display;
run;
%end;

ods excel options(autofilter="ALL" sheet_name="B");
%if &ds=B %then %do;
proc report data=&ds;
column region;
define region/display;
run;
%end;

%mend;
%tt (A);
%tt(B);

/*Layer O*/
ods excel close;
ods results on;
ods listing;
/*-----Dataset C- to suppress the output----*/
%tt(C);

[purpleclothlady]

hi Tom:
Thanks for reminder, I give an example not the full codes, but next time I will make the detail more accurate.