topic Re: ODS EXCEL Sheet_interval with macro in SAS Programming

ODS EXCEL Sheet_interval with macro

elsfy — Thu, 12 May 2022 12:22:33 GMT

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

Re: ODS EXCEL Sheet_interval with macro

PaigeMiller — Thu, 12 May 2022 12:37:19 GMT

/*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;

Re: ODS EXCEL Sheet_interval with macro

elsfy — Thu, 12 May 2022 12:55:46 GMT

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

Re: ODS EXCEL Sheet_interval with macro

PaigeMiller — Thu, 12 May 2022 13:13:49 GMT

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

%put &sysvlong4;

Re: ODS EXCEL Sheet_interval with macro

elsfy — Thu, 12 May 2022 13:03:51 GMT

9.04.01M7P08062020

Re: ODS EXCEL Sheet_interval with macro

PaigeMiller — Thu, 12 May 2022 13:14:03 GMT

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.

Re: ODS EXCEL Sheet_interval with macro

elsfy — Thu, 12 May 2022 13:28:38 GMT

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.

Re: ODS EXCEL Sheet_interval with macro

ballardw — Thu, 12 May 2022 13:29:56 GMT

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.

Re: ODS EXCEL Sheet_interval with macro

Tom — Thu, 12 May 2022 15:12:55 GMT

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") ;

Re: ODS EXCEL Sheet_interval with macro

elsfy — Thu, 12 May 2022 15:11:40 GMT

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 ;

Re: ODS EXCEL Sheet_interval with macro

elsfy — Thu, 12 May 2022 15:34:33 GMT

"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 ?

Re: ODS EXCEL Sheet_interval with macro

Tom — Thu, 12 May 2022 15:49:27 GMT

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.

Re: ODS EXCEL Sheet_interval with macro

elsfy — Thu, 12 May 2022 16:08:21 GMT

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 ;

Re: ODS EXCEL Sheet_interval with macro

Tom — Thu, 12 May 2022 16:23:12 GMT

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.