how to create a new sheet only on byval1 in proc report using ods excel

[acordes]

The following code extract creates for each cod_mrc and _fec_unic combination a worksheet.

But I want to add a sheet only triggered by the first by group, here cod_mrc.

A workaround is to eliminate the second by group from the by statement and use it in the where clause (repeating the proc report block 5 times) so that I get 5 months of 2023 stacked in one sheet. 

But is there an easy way? 

ods _all_ close;
ods excel file='/caslibs/marketing/compared3.xlsx';
ods excel options( sheet_name='Brand: #byval1'); 

options missing=0;
proc report data=ONAIR.SUM_HIERARCHY2(rename=('_Ventas Retail VN'n=fin '_Uds Retail VN'n=ren 'Ventas Retail VN'n=myfin 'Uds Retail VN'n=myren )) spanrows 
nowd center out=three completerows;
by cod_mrc _fec_unic ;
where year(_fec_unic) = 2023 and _fec_unic le '30apr2023'd ;
format _fec_unic monyy.;

[Tom]

I am not sure I follow what you are asking for.

But why not just remove the second variable from the BY statement?

If you want the sheet to change use the SHEET_INTERVAL option of ODS EXCEL.