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

acordes — Tue, 23 May 2023 16:54:34 GMT

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.;

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

Tom — Tue, 23 May 2023 17:03:51 GMT

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.

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

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

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