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
/*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;
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
Repeating: Run this command and let us know what the response is:
%put &sysvlong4;
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.
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.
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.
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 ;
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") ;
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.
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 ;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.