proc report, dcl odsout obj() in the context of bygroup

[acordes]

calling @Cynthia_sas and @Kurt_Bremser 

I can manage to have each bygroup getting output in its own worksheet.

In my understanding it's the default behavior using the by option in proc report.

But I want to have another object below the report table, like a mosaic plot from proc freq.

So my idea is to use the dcl odsout obj() in combination with data _null_ and toggling sheet_output='none' and 'output'.

But this approach puts all groups in the same worksheet and appends a mosaic plot for the last group.

I can prepare data for following my question, but I think it's not necessary here because everything is ok except having both objects in one worksheet for each group.

Can I put the data _null_ step inside the proc report using the _break_ variable?

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

proc report data=PUBLIC.ALL_MONTHLY_copy nowd center split="*" out=three completerows;
where lowcase(producto) ne "other";
by from_brand ;
format 'month'n monyy.;
column producto year,  (fin pct_fin ren pct_ren mkpi )  kpi ;
define year /  center order=internal across;
define pct_ren / 'Share Ren' computed format=percent9.1;
define pct_fin / 'Share Fin' computed format=percent9.1;
define producto / group 'product' center;
define fin / analysis sum  'Ven.' ;
define ren / analysis sum  'Ren.'   ;
/* define tot / analysis sum  'YTD Ren.' ; */
/* define tot1 / analysis sum  'YTD Ven.' ; */
define kpi / computed f=percent9.1 'delta vs prev. YTD' style(column)={background=ampel.};
define mkpi / computed f=percent9.1 'KPI' style(column)={background=fpcta.};

   rbreak after / summarize;

   /*Add explanatory text to first summary row*/
   compute before;
	ren_total2019 = _c4_;
	   ren_total2020 = _c9_;
	   ren_total2021 = _c14_;
	fin_total2019 = _c2_;
	   fin_total2020 = _c7_;
	   fin_total2021 = _c12_;
   endcomp;

compute pct_fin;
_c3_=_c2_/fin_total2019;
_c8_=_c7_/fin_total2020;
_c13_=_c12_/fin_total2021;
endcomp;

compute pct_ren;
_c5_=_c4_/ren_total2019;
_c10_=_c9_/ren_total2020;
_c15_=_c14_/ren_total2021;
endcomp;



compute kpi;
kpi = _c16_ - _c11_;
endcomp;

compute mkpi;
_c6_=_c4_/_c2_;_c11_=_c9_/_c7_;_c16_=_c14_/_c12_;
endcomp;

run;

proc freq data=PUBLIC.ALL_MONTHLY_copy2;
table from_brand*producto / plots=mosaicplot;
run;

ods excel options(sheet_interval='output');
  ods exclude all;
 data _null_;
  dcl odsout obj();
 run;
 ods select all;

ods excel close;

[acordes]

I've tried to use the proc freq and the worksheet output inside the compute after block...

It doesn't result in error, but it creates many more worksheets.

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

proc report data=PUBLIC.ALL_MONTHLY_copy nowd center split="*" out=three completerows;
where lowcase(producto) ne "other";
by from_brand ;
format 'month'n monyy.;
column producto year,  (fin pct_fin ren pct_ren mkpi )  kpi ;
define year /  center order=internal across;
define pct_ren / 'Share Ren' computed format=percent9.1;
define pct_fin / 'Share Fin' computed format=percent9.1;
define producto / group 'product' center;
define fin / analysis sum  'Ven.' ;
define ren / analysis sum  'Ren.'   ;
/* define tot / analysis sum  'YTD Ren.' ; */
/* define tot1 / analysis sum  'YTD Ven.' ; */
define kpi / computed f=percent9.1 'delta vs prev. YTD' style(column)={background=ampel.};
define mkpi / computed f=percent9.1 'KPI' style(column)={background=fpcta.};

   rbreak after / summarize;

   /*Add explanatory text to first summary row*/
   compute before;
	ren_total2019 = _c4_;
	   ren_total2020 = _c9_;
	   ren_total2021 = _c14_;
	fin_total2019 = _c2_;
	   fin_total2020 = _c7_;
	   fin_total2021 = _c12_;
   endcomp;

compute pct_fin;
_c3_=_c2_/fin_total2019;
_c8_=_c7_/fin_total2020;
_c13_=_c12_/fin_total2021;
endcomp;

compute pct_ren;
_c5_=_c4_/ren_total2019;
_c10_=_c9_/ren_total2020;
_c15_=_c14_/ren_total2021;
endcomp;



compute kpi;
kpi = _c16_ - _c11_;
endcomp;

compute mkpi;
_c6_=_c4_/_c2_;_c11_=_c9_/_c7_;_c16_=_c14_/_c12_;
endcomp;

compute after;
if _break_="_RBREAK_" then do;
call execute('proc freq data=PUBLIC.ALL_MONTHLY_copy2;
table from_brand*producto / plots=mosaicplot;
run;
ods excel options(sheet_interval="output");
  ods exclude all;
 data _null_;
  dcl odsout obj();
 run;
 ods select all;');
end;
endcomp;
run;

ods excel close;

[Cynthia_sas · Accepted solution]

Hi:
No, you cannot put the DATA _NULL_ step inside the PROC REPORT. It's not entirely clear to me what you're doing. Is there going to be a separate mosaic plot for each value of from_brand. The way your current code works is that PROC REPORT will generate ALL the output before the PROC FREQ runs. So if your PROC FREQ produces 1 mosaic plot, then it would be after the final output from PROC REPORT. If your PROC FREQ produces more than one mosaic plot, then ALL the mosaic plots would be inserted AFTER ALL the PROC REPORT output. It looks like your PROC REPORT step is being asked to create 1 sheet for every value of the BY group variable. That does not have anything to do with PROC FREQ. Since the mosaic plot is happening after ALL the PROC REPORT output has been written to the sheets, then I would expect the plots to come after.
If you need a plot on the sheet for every by group, then you may need to perform some macro processing so that you run the entire REPORT/FREQ sequence for each value of the BY group variable.
Cynthia

[acordes]

Thanks Cynthia.
A macro construct should work. I've only 5 different bygroup levels, so hard-coded repetition of the proc report plus proc sgplot using an appropriate where clause should be fine as well.
I'll promote the usage of proc report in my company. It's such a time saver. So many employees are dedicating a lot of time for creating excel reports. With the additional downside of typing errors, copy-paste errors, wrong usage of filters and the huge size of the file carrying all the detail data.
Proc report rocks and I've seen that in Viya it runs on the cas aggregate action. So it's really fast.