ODS EXCEL Sheet_interval with macro

[elsfy]

Hi,

I am repeating a proc report with a macro in which parameters are different. 

ods excel file = "...xlsx" 
    options (sheet_name = &pol.) ; 

%macro report (pol,category) ; 

proc report data = a_&pol._&category. ;

columns .. ; 
define ... ; 
run ; 
%mend ; 
%report(pol=A1,category=X) ;
%report(pol=A1,category=Y);
%report(pol=A1,category=Z);
/* I WANT THESE 3 REPORTS ABOVE IN THE SAME EXCEL SHEET*/

/*NOW I WANT THESE 3 OTHERS IN AN OTHER SHEET  SO I TRIED THIS CODE BELOW BUT IT DIDN'T WORK : */

ods excel options(sheet_interval="now") ; 
%report(pol=A2,category=X) ;
%report(pol=A2,category=Y);
%report(pol=A2,category=Z);ods excel close ;

So i want one sheet for each POL i.e 3 tables x 2 sheets.

Thanks

[PaigeMiller]

/*NOW I WANT THESE 3 OTHERS IN AN OTHER SHEET  SO I TRIED THIS CODE BELOW BUT IT DIDN'T WORK : */

Please explain what did happen. Also, was there an error in the log? If so, what?

Finally, what version of SAS are you running? Run this command and let us know what the response is:

%put &sysvlong4;

[elsfy]

It's v9.4 of SAS EG.

What happened is that it created an excel file but for each macro (each pol and each category) but it seems like it was always the same category for each pol

[PaigeMiller]

Repeating: Run this command and let us know what the response is:

%put &sysvlong4;

[elsfy]

9.04.01M7P08062020

[PaigeMiller]

What happened is that it created an excel file but for each macro (each pol and each category) but it seems like it was always the same category for each pol

I don't understand this sentence. The code you presented can only create one Excel file. Can you please explain further.

[elsfy]

Sorry i forgot some words in my sentence. An excel file was created with multiple sheets. Each sheet corresponding to a pol and a category.

[ballardw]

Provide the full definition of your proc report. What you want may be possible without any macro programming at all. Especially if you are doing the same combination of categories for each level of POL. Sheet_Interval='Bygroup' and using BY POL; in the report might be all you need.

Example data, or program using an available SAS data set supplied in the SASHELP library, will help a lot.

[elsfy]

I get it but for my purpose i need to do a macro because for the 3rd POL value (i know ive only shown 2 POL values but i have more), i don't want to display a variable and if i don't do a macro, then it's going to be displayed (even if its values would be set to 0) for this POL. This is a variable that i want to display for all POL values except one. I hope it's not too confusing

ods excel file = "...xlsx" 
    options (sheet_name = &pol.) ; 
%let noprint = noprint;
%macro report (pol,category,noprint) ; 

proc report data = a_&pol._&category. ;

columns .. ; 
define ... ; 
define var_x / &noprint. ; 
run ; 
%mend ; 
%report(pol=A1,category=X) ;
%report(pol=A1,category=Y);
%report(pol=A1,category=Z);
ods excel options(sheet_interval="now") ; 
%report(pol=A2,category=X) ;
%report(pol=A2,category=Y);
%report(pol=A2,category=Z);
ods excel options(sheet_interval="now") ; 
%report(pol=A3,category=X,noprint=&noprint.) ;
%report(pol=A4,category=Y,noprint=&noprint.);
%report(pol=A5,category=Z,noprint=&noprint.);
ods excel close ;

[Tom]

It seems to me you just want to change the sheet interval and sheet name options at the appropriate times.

Fix the ordering of your code to make it easier to understand. Don't mix the macro definitions into the middle of the executable code. Define the macro(s) first and then starting writing the code that will actually be run. 

What names should the sheets have?

Based on this description 

NOW

creates a new worksheet. When used, the next output object starts on a new sheet. After SHEET_INTERVAL='NOW' is executed, the SHEET_INTERVAL option reverts to the previous setting.

So this should work.

ods excel file = "...xlsx" ;
ods excel options (sheet_interval="none") ; 

ods excel options (sheet_name = "A1_sheet" ) ; 
ods excel options (sheet_interval="now") ; 
%report(pol=A1,category=X) ;
%report(pol=A1,category=Y);
%report(pol=A1,category=Z);

ods excel options (sheet_name = "A2_sheet" ) ; 
ods excel options (sheet_interval="now") ; 
%report(pol=A2,category=X) ;
%report(pol=A2,category=Y);
%report(pol=A2,category=Z);

If it doesn't try sprinkling in more 

ods excel options(sheet_interval="none") ; 

[elsfy]

"It seems to me you just want to change the sheet interval and sheet name options at the appropriate times."
Yes it's exactly what i want to do. In fact, it might be an ordering problem in my case. Should i put the ODS EXCEL FILE before the %macro ?

[Tom]

NO.  It will just confuse YOU.  (SAS doesn't get confused since it is just software.)

The %MACRO ... %MEND block just defines the macro.  So during those lines no actual SAS code is running.  It is when you call the macro that it can emit SAS code that can run.

So open the ODS destination before you run the first step that will produce output.

And close it after the last output you want to include in that file.

[elsfy]

Thank you very much. Adapting the code like this worked : 

%macro report; 
proc report ; 
%mend ; 

ods excel file="" 
options(sheet_interval="none") ; 

ods excel options(sheet_name="A1") ; 
%report(..);
%report(..);
%report(..);

ods excel options(sheet_name="A2") ; 
ods excel options(sheet_interval="now") ; 
%report(..);
%report(..);
%report(..);

ods excel options(sheet_name="A3") ; 
ods excel options(sheet_interval="now") ; 
%report(..);
%report(..);
%report(..);
ods excel close ;

[Tom]

Right.  If you set the NOW option before anything has been written if kind of messes it up.

So you only want that before the 2nd, 3rd, etc sheets and not before the 1st sheet.