proc report summary for group at the end

[acordes]

@Cynthia_sas I'm calling the expert directly. I've learnt a lot from your papers and your answers.

I'm quite happy with the output but I'm struggling to get final summary lines for the 5 products I have at the end of the report before the grand total.

So the second group 'product' should create 5 lines at the end of the report summarizing for each product.

ods _all_ close;
ods excel file='/caslibs/marketing/vw_kpi_dealer_retail_report_v2.xlsx';
proc report data=public.sum2(rename=(fin_sum=fin ren_sum=ren)) nowd center split="*" out=three completerows;
where year(month) ge 2020 and lowcase(from_brand)="audi";
by from_brand year;
format 'month'n monyy.;
column from_dig5 show_from_dig5 producto  'month'n,   (fin ren mkpi ) fin=tot1  ren=tot kpi ;
define from_dig5 / group noprint  ;
define show_from_dig5 / computed f=$14. 'Dealer code' left;
define 'month'n / 'quarter*(1)' center order=internal across;
define producto / group 'product*(2)' center;
define fin / analysis sum  'Finalizados*(1)' ;
define ren / analysis sum  'Renovados*(2)'   ;
define tot / analysis sum  'YTD Renovados*(4)' ;
define tot1 / analysis sum  'YTD Finalizados*(5)' ;
define kpi / computed f=percent9.1 'ytd KPI*(6)' style(column)={background=fpcta.};
define mkpi / computed f=percent9.1 'KPI*(3)' style(column)={background=fpcta.};

break after from_dig5 / summarize style=Header;


  compute before from_dig5;
    length holdval $25;
	holdval = from_dig5;
  endcomp;
  compute show_from_dig5/character length=25;
    show_from_dig5 = holdval;
	if upcase(_break_) = 'FROM_DIG5' then
	  show_from_dig5 = catx(' ', show_from_dig5,'Total');
  endcomp;

compute kpi;
kpi = tot / tot1;
endcomp;

compute mkpi;
_c6_=_c5_/_c4_;_c9_=_c8_/_c7_;_c12_=_c11_/_c10_;_c15_=_c14_/_c13_;_c18_=_c17_/_c16_;_c21_=_c20_/_c19_;_c24_=_c23_/_c22_;
_c27_=_c26_/_c25_;_c30_=_c29_/_c28_;_c33_=_c32_/_c31_;_c36_=_c35_/_c34_;_c39_=_c38_/_c37_;
endcomp;

rbreak after / summarize ul ol;

run;

ods _all_ close;
ods listing;

[Cynthia_sas]

Hi:

  Sorry for not responding sooner. Apparently the "@mention" is more than a "like". I'm not sure I really understand your report. Your code is nice to see, but since the structure of the data makes a difference, it would be useful to have test data. I don't understand why you are using named literal for the month variable ('month'n) when the variable name does not have any special characters or spaces. You don't show your format. Not clear what the numbers on the columns mean. Based on your COMPUTE block, I would expect many more columns than you show in your screen shot especially if your MKPI item is being calculated under the ACROSS down to _C39_ -- that is a much wider report than your screen shot shows.Without seeing the data, I'm not sure why you're using COMPLETEROWS, or why you are using the OUT= option. You have 2 break lines: a BREAK after from_dig5 and an RBREAK at the bottom of the report. Without knowing what you expect, the 2 statements seem to me to be working correctly. What else do you expect?

  Why is Dealer redacted or made black. I don't see that in your program?  Also, options like OL and UL on the BREAK or RBREAK statements are completely ignored by ODS destinations.

  Finally, I have no idea, at all what you mean when you say you want the " final summary lines for the 5 products I have at the end of the report before the grand total." What are you expecting to see. It looks like BREAK and RBREAK are working correctly to me.

  Sorry I only have questions. But perhaps my questions will point you toward more of a solution.

Cynthia

[acordes]

My fault.

I blacked these cells due to confidentiality concerns.

It has 12 months for a full year, but I showed only 2021 with one month.

So I update the code and add fictitious data created with the data2datastep trick (thank you @SASJedi, works like a charm).

@Cynthia_sas I want the second group variable (product) to have a global summary before ending with the grand total. 

data PUBLIC.SUM4;
  infile datalines dsd truncover;
  input producto:$20. from_dig5:$5. month:MONYY. year:32. _TYPE_:32. _FREQ_:32. ren_Sum:BEST12. fin_Sum:BEST12. kpi:32. from_dig:32.;
datalines4;
Lineal,X97 3,JAN20,2020,31,10,,10,,
Lineal,X97 3,FEB20,2020,31,14,,14,,
Lineal,X97 3,MAR20,2020,31,9,,9,,
Lineal,X97 3,APR20,2020,31,6,,6,,
Lineal,X97 3,MAY20,2020,31,9,,9,,
Lineal,X97 3,JUN20,2020,31,6,1,5,0.2,
Lineal,X97 3,JUL20,2020,31,11,1,10,0.1,
Lineal,X97 3,AUG20,2020,31,4,,4,,
Lineal,X97 3,SEP20,2020,31,7,,7,,
Lineal,X97 3,OCT20,2020,31,9,,9,,
Lineal,X97 3,NOV20,2020,31,8,,8,,
Lineal,X97 3,DEC20,2020,31,8,1,7,0.1428571429,
Lineal,X97 3,JAN21,2021,31,9,,9,,
Lineal,X97 3,JAN20,2020,31,11,2,9,0.2222222222,
Lineal,X97 3,FEB20,2020,31,7,,7,,
Lineal,X97 3,MAR20,2020,31,10,,10,,
Lineal,X97 3,APR20,2020,31,5,,5,,
Lineal,X97 3,MAY20,2020,31,4,,4,,
Lineal,X97 3,JUN20,2020,31,7,,7,,
Lineal,X97 3,JUL20,2020,31,10,,10,,
Lineal,X97 3,AUG20,2020,31,8,,8,,
Lineal,X97 3,SEP20,2020,31,7,1,6,0.1666666667,
Lineal,X97 3,OCT20,2020,31,8,,8,,
Lineal,X97 3,NOV20,2020,31,10,,10,,
Lineal,X97 3,DEC20,2020,31,9,1,8,0.125,
Lineal,X97 3,JAN21,2021,31,7,,7,,
Lineal,X00 3,JAN20,2020,31,18,2,16,0.125,
Lineal,X00 3,FEB20,2020,31,15,1,14,0.0714285714,
Lineal,X00 3,MAR20,2020,31,12,,12,,
Lineal,X00 3,APR20,2020,31,16,,16,,
Lineal,X00 3,MAY20,2020,31,17,,17,,
Lineal,X00 3,JUN20,2020,31,12,,12,,
Lineal,X00 3,JUL20,2020,31,33,3,30,0.1,
Lineal,X00 3,AUG20,2020,31,17,2,15,0.1333333333,
Lineal,X00 3,SEP20,2020,31,19,,19,,
Lineal,X00 3,OCT20,2020,31,16,,16,,
Lineal,X00 3,NOV20,2020,31,22,1,21,0.0476190476,
Lineal,X00 3,DEC20,2020,31,17,1,16,0.0625,
Lineal,X00 3,JAN21,2021,31,13,1,12,0.0833333333,
Lineal,X00 3,JAN20,2020,31,1,,1,,
Lineal,X00 3,FEB20,2020,31,2,,2,,
Lineal,X00 3,MAR20,2020,31,1,,1,,
Lineal,X00 3,APR20,2020,31,2,,2,,
Lineal,X00 3,MAY20,2020,31,1,,1,,
Lineal,X00 3,JUN20,2020,31,2,,2,,
Lineal,X00 3,JUL20,2020,31,8,,8,,
Lineal,X00 3,AUG20,2020,31,5,,5,,
Lineal,X00 3,SEP20,2020,31,7,,7,,
Lineal,X00 3,OCT20,2020,31,3,,3,,
Lineal,X00 3,NOV20,2020,31,5,,5,,
Lineal,X00 3,DEC20,2020,31,13,,13,,
Lineal,X00 3,JAN21,2021,31,2,,2,,
Lineal,X03 9,DEC20,2020,31,3,,3,,
Lineal,X08 3,JAN20,2020,31,1,,1,,
Lineal,X08 3,FEB20,2020,31,3,,3,,
Lineal,X08 3,MAR20,2020,31,4,,4,,
Lineal,X08 3,APR20,2020,31,3,,3,,
Lineal,X08 3,MAY20,2020,31,2,,2,,
Lineal,X08 3,JUN20,2020,31,3,,3,,
Lineal,X08 3,JUL20,2020,31,7,,7,,
Lineal,X08 3,AUG20,2020,31,2,,2,,
Lineal,X08 3,SEP20,2020,31,7,,7,,
Lineal,X08 3,OCT20,2020,31,1,,1,,
Lineal,X08 3,NOV20,2020,31,5,,5,,
Lineal,X08 3,DEC20,2020,31,2,,2,,
Other,X97 3,MAY20,2020,31,1,,1,,
Other,X97 3,JAN21,2021,31,1,,1,,
Renting,X92 3,APR20,2020,31,1,,1,,
Renting,X92 3,JUN20,2020,31,1,,1,,
Renting,X92 3,AUG20,2020,31,2,1,1,1,
Renting,X92 3,OCT20,2020,31,1,1,,,
Renting,X92 3,NOV20,2020,31,1,,1,,
Renting,X92 3,APR20,2020,31,1,,1,,
Renting,X92 3,JUL20,2020,31,1,,1,,
Renting,X92 3,SEP20,2020,31,1,,1,,
Renting,X92 3,NOV20,2020,31,1,1,,,
Renting,X92 3,DEC20,2020,31,1,,1,,
Renting,X92 3,JAN21,2021,31,2,1,1,1,
Lineal,X92 3,JAN20,2020,31,11,,11,,
Lineal,X92 3,FEB20,2020,31,10,,10,,
Lineal,X92 3,MAR20,2020,31,5,,5,,
Lineal,X92 3,APR20,2020,31,5,,5,,
Lineal,X92 3,MAY20,2020,31,7,,7,,
Lineal,X92 3,JUN20,2020,31,4,,4,,
Lineal,X92 3,JUL20,2020,31,8,,8,,
Lineal,X92 3,AUG20,2020,31,6,,6,,
Lineal,X92 3,SEP20,2020,31,9,,9,,
Lineal,X92 3,OCT20,2020,31,11,,11,,
Lineal,X92 3,NOV20,2020,31,14,,14,,
Lineal,X92 3,DEC20,2020,31,5,,5,,
Lineal,X92 3,JAN21,2021,31,6,,6,,
Lineal,X92 3,JAN20,2020,31,17,,17,,
Lineal,X92 3,FEB20,2020,31,16,2,14,0.1428571429,
Lineal,X92 3,MAR20,2020,31,19,,19,,
Lineal,X92 3,APR20,2020,31,17,,17,,
Lineal,X92 3,MAY20,2020,31,13,,13,,
Lineal,X92 3,JUN20,2020,31,24,2,22,0.0909090909,
Lineal,X92 3,JUL20,2020,31,28,,28,,
Lineal,X92 3,AUG20,2020,31,13,,13,,
Lineal,X92 3,SEP20,2020,31,21,1,20,0.05,
Lineal,X92 3,OCT20,2020,31,12,,12,,
Lineal,X92 3,NOV20,2020,31,21,,21,,
Lineal,X92 3,DEC20,2020,31,15,,15,,
Lineal,X92 3,JAN21,2021,31,17,,17,,
CPC,X92 3,FEB20,2020,31,1,,1,,
CPC,X92 3,MAR20,2020,31,1,,1,,
CPC,X92 3,JUN20,2020,31,1,,1,,
CPC,X92 3,SEP20,2020,31,2,,2,,
CPC,X92 3,NOV20,2020,31,1,,1,,
CPC,X92 3,DEC20,2020,31,2,,2,,
CPC,X92 3,JAN21,2021,31,1,,1,,
CPC,X92 3,JUN20,2020,31,1,,1,,
CPC,X92 3,JUL20,2020,31,2,,2,,
CPC,X92 3,AUG20,2020,31,1,,1,,
CPC,X92 3,SEP20,2020,31,1,,1,,
CPC,X97 3,JAN20,2020,31,1,1,,,
CPC,X97 3,FEB20,2020,31,1,,1,,
CPC,X97 3,JUN20,2020,31,1,,1,,
CPC,X97 3,JAN21,2021,31,1,,1,,
CPC,X97 3,MAY20,2020,31,2,,2,,
CPC,X97 3,JUN20,2020,31,1,,1,,
CPC,X97 3,AUG20,2020,31,1,,1,,
CPC,X97 3,SEP20,2020,31,1,,1,,
CPC,X00 3,JAN20,2020,31,1,,1,,
CPC,X00 3,MAR20,2020,31,2,1,1,1,
CPC,X00 3,APR20,2020,31,3,,3,,
CPC,X00 3,MAY20,2020,31,1,,1,,
CPC,X00 3,JUN20,2020,31,3,,3,,
CPC,X00 3,JUL20,2020,31,3,,3,,
CPC,X00 3,AUG20,2020,31,2,,2,,
CPC,X00 3,SEP20,2020,31,1,,1,,
CPC,X00 3,OCT20,2020,31,2,,2,,
CPC,X00 3,NOV20,2020,31,3,1,2,0.5,
CPC,X00 3,DEC20,2020,31,1,,1,,
CPC,X00 3,FEB20,2020,31,1,,1,,
CPC,X00 3,APR20,2020,31,1,,1,,
CPC,X00 3,JUN20,2020,31,2,,2,,
CPC,X00 3,SEP20,2020,31,1,,1,,
CPC,X00 3,NOV20,2020,31,1,,1,,
CPC,X08 3,JAN20,2020,31,1,,1,,
CPC,X08 3,JUN20,2020,31,1,,1,,
CPC,X08 3,AUG20,2020,31,1,,1,,
Lineal,X94 3,JAN20,2020,31,2,,2,,
Lineal,X94 3,FEB20,2020,31,2,,2,,
Lineal,X94 3,MAR20,2020,31,3,,3,,
Lineal,X94 3,APR20,2020,31,1,,1,,
Lineal,X94 3,MAY20,2020,31,3,,3,,
Lineal,X94 3,JUN20,2020,31,4,,4,,
Lineal,X94 3,JUL20,2020,31,2,,2,,
Lineal,X94 3,AUG20,2020,31,7,,7,,
Lineal,X94 3,SEP20,2020,31,1,,1,,
Lineal,X94 3,NOV20,2020,31,1,,1,,
Lineal,X94 3,DEC20,2020,31,2,,2,,
Lineal,X94 3,JAN21,2021,31,3,,3,,
Renting,X97 3,JAN20,2020,31,2,1,1,1,
Renting,X97 3,FEB20,2020,31,1,,1,,
Renting,X97 3,MAR20,2020,31,1,,1,,
Renting,X97 3,APR20,2020,31,1,,1,,
Renting,X97 3,MAY20,2020,31,1,1,,,
Renting,X97 3,JUN20,2020,31,1,,1,,
Renting,X97 3,JUL20,2020,31,1,1,,,
Renting,X97 3,SEP20,2020,31,3,2,1,2,
Renting,X97 3,OCT20,2020,31,2,,2,,
Renting,X97 3,NOV20,2020,31,3,,3,,
Renting,X97 3,DEC20,2020,31,2,,2,,
Renting,X97 3,JAN21,2021,31,2,,2,,
Renting,X97 3,JUN20,2020,31,1,,1,,
Renting,X97 3,JAN21,2021,31,1,,1,,
Renting,X00 3,JAN20,2020,31,1,,1,,
Renting,X00 3,JUL20,2020,31,1,,1,,
Renting,X00 3,AUG20,2020,31,1,1,,,
Renting,X00 3,SEP20,2020,31,1,,1,,
Renting,X00 3,OCT20,2020,31,1,,1,,
Renting,X00 3,NOV20,2020,31,1,,1,,
Renting,X00 3,JAN21,2021,31,1,,1,,
Renting,X00 3,APR20,2020,31,1,,1,,
Renting,X03 8,AUG20,2020,31,1,,1,,
Renting,X08 3,FEB20,2020,31,1,,1,,
Renting,X08 3,OCT20,2020,31,1,1,,,
Renting,X08 3,NOV20,2020,31,1,,1,,
;;;;
run;


ods _all_ close;
ods excel file='/caslibs/marketing/test_kpi_dealer_retail_report_v2.xlsx';
proc report data=public.sum4(rename=(fin_sum=fin ren_sum=ren)) nowd center split="*" out=three completerows;
by year;
format 'month'n monyy.;
column from_dig5 show_from_dig5 producto  'month'n,   (fin ren mkpi ) fin=tot1  ren=tot kpi ;
define from_dig5 / group noprint  ;
define show_from_dig5 / computed f=$14. 'Dealer code' left;
define 'month'n / 'quarter*(1)' center order=internal across;
define producto / group 'product*(2)' center;
define fin / analysis sum  'Finalizados*(1)' ;
define ren / analysis sum  'Renovados*(2)'   ;
define tot / analysis sum  'YTD Renovados*(4)' ;
define tot1 / analysis sum  'YTD Finalizados*(5)' ;
define kpi / computed f=percent9.1 'ytd KPI*(6)' style(column)={background=fpcta.};
define mkpi / computed f=percent9.1 'KPI*(3)' style(column)={background=fpcta.};

break after from_dig5 / summarize style=Header;


  compute before from_dig5;
    length holdval $25;
	holdval = from_dig5;
  endcomp;
  compute show_from_dig5/character length=25;
    show_from_dig5 = holdval;
	if upcase(_break_) = 'FROM_DIG5' then
	  show_from_dig5 = catx(' ', show_from_dig5,'Total');
	if upcase(_break_) = '_RBREAK_' then
	  show_from_dig5 = 'Grand Total';
  endcomp;

compute kpi;
kpi = tot / tot1;
endcomp;

compute mkpi;
_c6_=_c5_/_c4_;_c9_=_c8_/_c7_;_c12_=_c11_/_c10_;_c15_=_c14_/_c13_;_c18_=_c17_/_c16_;_c21_=_c20_/_c19_;_c24_=_c23_/_c22_;
_c27_=_c26_/_c25_;_c30_=_c29_/_c28_;_c33_=_c32_/_c31_;_c36_=_c35_/_c34_;_c39_=_c38_/_c37_;
endcomp;

rbreak after / summarize ul ol;

run;

ods _all_ close;
ods listing;

[Cynthia_sas · Accepted solution]

Hi:
-- don't have format fpcta for style override on background in your code
-- get error because public.sum4 is not sorted by year
-- KPI variable is created in your INPUT statement, but you also have KPI listed as COMPUTED in the PROC REPORT step. This generates NOTES that indicate output might not be desirable and I agree...I don't understand why you're reading KPI from the input file and then overwriting it with your own calculation. These are the NOTES:
NOTE: The computed variable kpi is also a data set variable.
NOTE: The output might not be as expected.
-- even correcting for some of the above, I have NO idea what you mean by a "global summary" of the products before the grand total. That is not what PROC REPORT will do. In your test data, I see 4 unique values for PRODUCTO:
producto
CPC 38
Lineal 103
Other 2
Renting 37

The way you have your report structured, you have THIS in your column statement:
column from_dig5 show_from_dig5 producto month, (fin ren mkpi ) fin=tot1 ren=tot kpi ;

So even if FROM_DIG5 is NOPRINT, it is still a GROUP item and the PRODUCTO values are nested "inside" each FROM_DIG5 group. That is what I see. The way you have your COLUMN statement is not going to allow a separate summary for PRODUCTO.

Correcting for all the missing parts of your code and ignoring ODS EXCEL in the interests of simplicity, here's what I get for 2021 -- subtotals are yellow and Grand total is blue like the headers:

I think you might envision some kind of separate summary for PRODUCTO without regard to FROM_DIG5, maybe between the last yellow subtotal and the final GRAND TOTAL but that would be a separate PROC REPORT step, if you did not want FROM_DIG5 to be used as the primary group, then you'd need to drop it and the other logic from your code. Here's an example of what I envision by "global summary" of PRODUCTO for 2021 just showing summaries for PRODUCTO without regard to FROM_DIG5:

Maybe this will help you figure out what you can and can't do with PROC REPORT.

Cynthia


:

[acordes]

That was my question.
And the answer is clear. I have to do it separately.
" if you did not want FROM_DIG5 to be used as the primary group, then you'd need to drop it and the other logic from your code. Here's an example of what I envision by "global summary" of PRODUCTO for 2021 just showing summaries for PRODUCTO without regard to FROM_DIG5:"