Proc report compute before - across collision

[acordes]

I'm quite close to what's my target picture. 

But I get too many lines in the output. 

I carry over the month's value in a across format because I'll need them to compute a 'vs last month' figure (still missing in the photo attachment and the code block). 

For the moment it would be okay to have the ytd_kpi and the ref_kpi (which are correctly calculated) only appearing once and not 10 times more corresponding to the 10 months ytd which in other similar code (surpressing the 'noprint' option in the month variable) goes to the columns. 

proc format;
value fpcta
.='white'
0-<0.05 = 'verylightred'
0.05-<0.1 = 'verylightyellow'
0.1-<0.3='lightgreen'
0.3-high='green';
run;

proc report data=public.sum2_ch nowd center split="*" out=three  ;
/* where year ge 2021 and producto ne "Other" and from_brand="Audi"; */
where year ge 2021 and producto ne "Other";
by from_brand year;
format 'month'n monyy.;
column ref_group from_conc show_from_dig5 region  NOMBRE_DELEGADO   producto  'month'n,   (fin ren mkpi ) fin=tot1  ren=tot kpi ref_kpi ;
define ref_group /  group noprint;
define region /  group  ;
define NOMBRE_DELEGADO /  group  ;
define from_conc / group noprint order  ;
define show_from_dig5 / computed f=$80. 'Dealer code' left;
define 'month'n /  center order=internal across noprint ;
define producto / group 'product' center order;
define fin / analysis sum  'Ven.' noprint ;
define ren / analysis sum  'Ren.'  noprint ;
define tot / analysis sum  'YTD Ren.' ;
define tot1 / analysis sum  'YTD Ven.' ;
define kpi / computed f=percent9.1 'ytd KPI' style(column)={background=fpcta.};
define mkpi / computed f=percent9.1 'KPI' style(column)={background=fpcta.} noprint;
define ref_kpi / computed f=percent9.1;

compute before ref_group;
_ref_tot=tot;
_ref_tot1=tot1;
endcomp;

compute ref_kpi;
ref_kpi=_ref_tot/_ref_tot1;
endcomp;

break after from_conc / summarize ;

  compute before from_conc;
    length holdval $25;
	holdval = substr(from_conc,4,index(substrn(from_conc,10,100),"|")+5)||"                   ";
  endcomp;
  compute show_from_dig5/character length=80;
    show_from_dig5 = holdval;
	if upcase(_break_) = 'FROM_CONC' then
	  show_from_dig5 = catx(' ', show_from_dig5,'Total');
	if upcase(_break_) = '_RBREAK_' then
	  show_from_dig5 = 'Grand Total';
  endcomp;

compute region;
  length holdreg $15;
  if region ne ' ' then holdreg = region;
  else if region = ' ' and _break_ = ' ' then region = holdreg;
endcomp;

compute nombre_delegado;
  length holddel $50;
  if nombre_delegado ne ' ' then holddel = nombre_delegado;
  else if nombre_delegado = ' ' and _break_ = ' ' then nombre_delegado = holddel;
endcomp;


compute kpi;
kpi = tot / tot1;
endcomp;

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

rbreak after / summarize ul ol;

run;

data PUBLIC.SUM2_CH;
  infile datalines dsd truncover;
  input month:MONYY. year:32. REGION:$20. _TYPE_:32. _FREQ_:32. ren:BEST12. fin:32. ref_group:$200. from_conc:$10. NOMBRE_DELEGADO:$10. producto:$10. from_brand:$10.;
datalines4;
OCT21,2021,ESTE,127,5,1,4,chiffre x 53ABA3ABC6,C652F22CD1,41D2259800,53ABA3ABC6,chiffre
APR21,2021,ESTE,127,10,1,9,chiffre x 53ABA3ABC6,C652F22CD1,41D2259800,53ABA3ABC6,chiffre
SEP21,2021,ESTE,127,8,3,5,chiffre x 53ABA3ABC6,C652F22CD1,41D2259800,53ABA3ABC6,chiffre
FEB21,2021,ESTE,127,7,1,6,chiffre x 53ABA3ABC6,C652F22CD1,41D2259800,53ABA3ABC6,chiffre
JUN21,2021,ESTE,127,15,4,11,chiffre x 53ABA3ABC6,C652F22CD1,41D2259800,53ABA3ABC6,chiffre
AUG21,2021,ESTE,127,3,,3,chiffre x 53ABA3ABC6,C652F22CD1,41D2259800,53ABA3ABC6,chiffre
MAR21,2021,ESTE,127,17,2,15,chiffre x 53ABA3ABC6,C652F22CD1,41D2259800,53ABA3ABC6,chiffre
JAN21,2021,ESTE,127,7,,7,chiffre x 53ABA3ABC6,C652F22CD1,41D2259800,53ABA3ABC6,chiffre
JUL21,2021,ESTE,127,14,3,11,chiffre x 53ABA3ABC6,C652F22CD1,41D2259800,53ABA3ABC6,chiffre
MAY21,2021,ESTE,127,9,5,4,chiffre x 53ABA3ABC6,C652F22CD1,41D2259800,53ABA3ABC6,chiffre
APR21,2021,ESTE,127,11,1,10,chiffre x 9F15AD2290,C652F22CD1,41D2259800,9F15AD2290,chiffre
FEB21,2021,ESTE,127,9,,9,chiffre x 9F15AD2290,C652F22CD1,41D2259800,9F15AD2290,chiffre
MAR21,2021,ESTE,127,11,,11,chiffre x 9F15AD2290,C652F22CD1,41D2259800,9F15AD2290,chiffre
JAN21,2021,ESTE,127,11,,11,chiffre x 9F15AD2290,C652F22CD1,41D2259800,9F15AD2290,chiffre
MAY21,2021,ESTE,127,8,,8,chiffre x 9F15AD2290,C652F22CD1,41D2259800,9F15AD2290,chiffre
OCT21,2021,ESTE,127,9,,9,chiffre x 9F15AD2290,C652F22CD1,41D2259800,9F15AD2290,chiffre
SEP21,2021,ESTE,127,8,,8,chiffre x 9F15AD2290,C652F22CD1,41D2259800,9F15AD2290,chiffre
AUG21,2021,ESTE,127,3,,3,chiffre x 9F15AD2290,C652F22CD1,41D2259800,9F15AD2290,chiffre
JUL21,2021,ESTE,127,19,1,18,chiffre x 9F15AD2290,C652F22CD1,41D2259800,9F15AD2290,chiffre
JUN21,2021,ESTE,127,8,,8,chiffre x 9F15AD2290,C652F22CD1,41D2259800,9F15AD2290,chiffre
OCT21,2021,ESTE,127,0,,,chiffre x E36CDE360C,C652F22CD1,41D2259800,E36CDE360C,chiffre
SEP21,2021,ESTE,127,1,,1,chiffre x E36CDE360C,C652F22CD1,41D2259800,E36CDE360C,chiffre
AUG21,2021,ESTE,127,1,,1,chiffre x E36CDE360C,C652F22CD1,41D2259800,E36CDE360C,chiffre
JUL21,2021,ESTE,127,0,,,chiffre x E36CDE360C,C652F22CD1,41D2259800,E36CDE360C,chiffre
JUN21,2021,ESTE,127,0,,,chiffre x E36CDE360C,C652F22CD1,41D2259800,E36CDE360C,chiffre
MAY21,2021,ESTE,127,0,,,chiffre x E36CDE360C,C652F22CD1,41D2259800,E36CDE360C,chiffre
APR21,2021,ESTE,127,0,,,chiffre x E36CDE360C,C652F22CD1,41D2259800,E36CDE360C,chiffre
MAR21,2021,ESTE,127,0,,,chiffre x E36CDE360C,C652F22CD1,41D2259800,E36CDE360C,chiffre
FEB21,2021,ESTE,127,0,,,chiffre x E36CDE360C,C652F22CD1,41D2259800,E36CDE360C,chiffre
JAN21,2021,ESTE,127,0,,,chiffre x E36CDE360C,C652F22CD1,41D2259800,E36CDE360C,chiffre
JAN21,2021,ESTE,127,30,11,19,chiffre x 43C58C873B,C652F22CD1,41D2259800,43C58C873B,chiffre
OCT21,2021,ESTE,127,18,5,13,chiffre x 43C58C873B,C652F22CD1,41D2259800,43C58C873B,chiffre
AUG21,2021,ESTE,127,35,5,30,chiffre x 43C58C873B,C652F22CD1,41D2259800,43C58C873B,chiffre
JUL21,2021,ESTE,127,30,10,20,chiffre x 43C58C873B,C652F22CD1,41D2259800,43C58C873B,chiffre
JUN21,2021,ESTE,127,33,4,29,chiffre x 43C58C873B,C652F22CD1,41D2259800,43C58C873B,chiffre
MAY21,2021,ESTE,127,40,10,30,chiffre x 43C58C873B,C652F22CD1,41D2259800,43C58C873B,chiffre
APR21,2021,ESTE,127,26,8,18,chiffre x 43C58C873B,C652F22CD1,41D2259800,43C58C873B,chiffre
SEP21,2021,ESTE,127,28,5,23,chiffre x 43C58C873B,C652F22CD1,41D2259800,43C58C873B,chiffre
MAR21,2021,ESTE,127,32,9,23,chiffre x 43C58C873B,C652F22CD1,41D2259800,43C58C873B,chiffre
FEB21,2021,ESTE,127,24,7,17,chiffre x 43C58C873B,C652F22CD1,41D2259800,43C58C873B,chiffre
OCT21,2021,NORTE,127,33,10,23,chiffre x 53ABA3ABC6,E11282EE7C,E84FB2F8F1,53ABA3ABC6,chiffre
APR21,2021,NORTE,127,34,8,26,chiffre x 53ABA3ABC6,E11282EE7C,E84FB2F8F1,53ABA3ABC6,chiffre
SEP21,2021,NORTE,127,35,6,29,chiffre x 53ABA3ABC6,E11282EE7C,E84FB2F8F1,53ABA3ABC6,chiffre
FEB21,2021,NORTE,127,27,7,20,chiffre x 53ABA3ABC6,E11282EE7C,E84FB2F8F1,53ABA3ABC6,chiffre
JUN21,2021,NORTE,127,43,9,34,chiffre x 53ABA3ABC6,E11282EE7C,E84FB2F8F1,53ABA3ABC6,chiffre
AUG21,2021,NORTE,127,13,1,12,chiffre x 53ABA3ABC6,E11282EE7C,E84FB2F8F1,53ABA3ABC6,chiffre
MAR21,2021,NORTE,127,31,7,24,chiffre x 53ABA3ABC6,E11282EE7C,E84FB2F8F1,53ABA3ABC6,chiffre
JAN21,2021,NORTE,127,21,4,17,chiffre x 53ABA3ABC6,E11282EE7C,E84FB2F8F1,53ABA3ABC6,chiffre
JUL21,2021,NORTE,127,42,10,32,chiffre x 53ABA3ABC6,E11282EE7C,E84FB2F8F1,53ABA3ABC6,chiffre
MAY21,2021,NORTE,127,38,15,23,chiffre x 53ABA3ABC6,E11282EE7C,E84FB2F8F1,53ABA3ABC6,chiffre
APR21,2021,NORTE,127,16,1,15,chiffre x 9F15AD2290,E11282EE7C,E84FB2F8F1,9F15AD2290,chiffre
FEB21,2021,NORTE,127,15,,15,chiffre x 9F15AD2290,E11282EE7C,E84FB2F8F1,9F15AD2290,chiffre
MAR21,2021,NORTE,127,17,,17,chiffre x 9F15AD2290,E11282EE7C,E84FB2F8F1,9F15AD2290,chiffre
JAN21,2021,NORTE,127,18,,18,chiffre x 9F15AD2290,E11282EE7C,E84FB2F8F1,9F15AD2290,chiffre
MAY21,2021,NORTE,127,21,,21,chiffre x 9F15AD2290,E11282EE7C,E84FB2F8F1,9F15AD2290,chiffre
OCT21,2021,NORTE,127,19,,19,chiffre x 9F15AD2290,E11282EE7C,E84FB2F8F1,9F15AD2290,chiffre
SEP21,2021,NORTE,127,12,,12,chiffre x 9F15AD2290,E11282EE7C,E84FB2F8F1,9F15AD2290,chiffre
AUG21,2021,NORTE,127,21,,21,chiffre x 9F15AD2290,E11282EE7C,E84FB2F8F1,9F15AD2290,chiffre
JUL21,2021,NORTE,127,29,,29,chiffre x 9F15AD2290,E11282EE7C,E84FB2F8F1,9F15AD2290,chiffre
JUN21,2021,NORTE,127,33,1,32,chiffre x 9F15AD2290,E11282EE7C,E84FB2F8F1,9F15AD2290,chiffre
OCT21,2021,NORTE,127,3,1,2,chiffre x E36CDE360C,E11282EE7C,E84FB2F8F1,E36CDE360C,chiffre
SEP21,2021,NORTE,127,0,,,chiffre x E36CDE360C,E11282EE7C,E84FB2F8F1,E36CDE360C,chiffre
AUG21,2021,NORTE,127,2,1,1,chiffre x E36CDE360C,E11282EE7C,E84FB2F8F1,E36CDE360C,chiffre
JUL21,2021,NORTE,127,0,,,chiffre x E36CDE360C,E11282EE7C,E84FB2F8F1,E36CDE360C,chiffre
JUN21,2021,NORTE,127,0,,,chiffre x E36CDE360C,E11282EE7C,E84FB2F8F1,E36CDE360C,chiffre
MAY21,2021,NORTE,127,1,,1,chiffre x E36CDE360C,E11282EE7C,E84FB2F8F1,E36CDE360C,chiffre
APR21,2021,NORTE,127,0,,,chiffre x E36CDE360C,E11282EE7C,E84FB2F8F1,E36CDE360C,chiffre
MAR21,2021,NORTE,127,0,,,chiffre x E36CDE360C,E11282EE7C,E84FB2F8F1,E36CDE360C,chiffre
FEB21,2021,NORTE,127,0,,,chiffre x E36CDE360C,E11282EE7C,E84FB2F8F1,E36CDE360C,chiffre
JAN21,2021,NORTE,127,0,,,chiffre x E36CDE360C,E11282EE7C,E84FB2F8F1,E36CDE360C,chiffre
JAN21,2021,NORTE,127,22,6,16,chiffre x 43C58C873B,E11282EE7C,E84FB2F8F1,43C58C873B,chiffre
OCT21,2021,NORTE,127,35,9,26,chiffre x 43C58C873B,E11282EE7C,E84FB2F8F1,43C58C873B,chiffre
AUG21,2021,NORTE,127,78,12,66,chiffre x 43C58C873B,E11282EE7C,E84FB2F8F1,43C58C873B,chiffre
JUL21,2021,NORTE,127,69,16,53,chiffre x 43C58C873B,E11282EE7C,E84FB2F8F1,43C58C873B,chiffre
JUN21,2021,NORTE,127,75,22,53,chiffre x 43C58C873B,E11282EE7C,E84FB2F8F1,43C58C873B,chiffre
MAY21,2021,NORTE,127,91,18,73,chiffre x 43C58C873B,E11282EE7C,E84FB2F8F1,43C58C873B,chiffre
APR21,2021,NORTE,127,31,14,17,chiffre x 43C58C873B,E11282EE7C,E84FB2F8F1,43C58C873B,chiffre
SEP21,2021,NORTE,127,51,12,39,chiffre x 43C58C873B,E11282EE7C,E84FB2F8F1,43C58C873B,chiffre
MAR21,2021,NORTE,127,69,21,48,chiffre x 43C58C873B,E11282EE7C,E84FB2F8F1,43C58C873B,chiffre
FEB21,2021,NORTE,127,33,9,24,chiffre x 43C58C873B,E11282EE7C,E84FB2F8F1,43C58C873B,chiffre
;;;;
run;

[acordes · Accepted solution]

I've done the aggregation before and now it works. 

Nice feature to play around with unicode format for producing arrows. 

and the ods excel options allows to control for column width and filters,...

proc cas;
    fedSQL.execDirect / 
        query="create table public.sum22{options replace=true} as 
select a.*, b.ref_kpi, put(a.year,4.) as _year 
               from PUBLIC.sum2 a   join 
(select sum(ren)/sum(fin) as ref_kpi, ref_group, year from 
public.sum2 group by ref_group, year) b 
on a.ref_group=b.ref_group and a.year=b.year ";
quit;

proc format;
value colorchngu
0.005 - high  = "(*ESC*){unicode '25b2'x}"
low - -0.005 = "(*ESC*){unicode '25bc'x}"
-0.005 - 0.005 = "(*ESC*){unicode '25ac'x}"
;
run;

proc format;
value coloru
0.005 - high = 'green'
low - -0.005 = 'red'
0.005 - 0.005 = 'black';
run;

ods excel file='/caslibs/marketing/kpi_vic.xlsx';
 ods excel options( sheet_name='Brand: #byval1' hidden_columns='5-64' autofilter='1-4'
 absolute_column_width='30,10,30,10, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8
, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8' absolute_row_height='25' ); 

proc report data=public.sum22 nowd center split="*" out=three   ;
where year ge 2020 and month('month'n) <=10 and producto ne "Other" ;
by from_brand ;
format 'month'n monyy.;
column  from_conc show_from_dig5 region NOMBRE_DELEGADO producto   'month'n ,   (fin ren mkpi )    _year, ( fin=tot1 ren=tot (ytd) ref_kpi ) 
vs_last vs_last=_vs_last vs_ref vs_ref=_vs_ref  ;
define region /  group  ;
define NOMBRE_DELEGADO /  group  ;
define from_conc / group noprint  ;
define show_from_dig5 / computed f=$80. 'Dealer code' left;
define 'month'n /  center order=internal across  ;
define _year / across 'year'   ;
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 ytd /  computed f=percent9.1 style(column)={background=fpcta.}; */
define ytd /   'ytd kpi' f=percent9.1 computed;
define mkpi / computed f=percent9.1 'KPI' style(column)={background=fpcta.} ;
define ref_kpi / mean f=percent9.1;
define vs_last /   '' computed f=colorchngu. style={foreground=coloru.};
define vs_ref /   '' computed f=colorchngu. style={foreground=coloru.};
define _vs_last / computed  'vs last year'  f=percentN9.1 ;
define _vs_ref / computed   'vs 2021 brandXproduct'  f=percentN9.2 ;

break after from_conc / summarize ;

compute ytd;
_c72_=_c71_/_c70_;
_c68_=_c67_/_c66_;
endcomp;

compute vs_last;
vs_last=_c72_-_c68_;
endcomp;

compute vs_ref;
vs_ref=_c72_-_c73_;
endcomp;

compute _vs_last;
_vs_last=vs_last;
endcomp;
compute _vs_ref;
_vs_ref=vs_ref;
endcomp;


  compute before from_conc;
    length holdval $25;
	holdval = substr(from_conc,4,index(substrn(from_conc,10,100),"|")+5)||"                   ";
  endcomp;
  compute show_from_dig5/character length=80;
    show_from_dig5 = holdval;
	if upcase(_break_) = 'FROM_CONC' then do;
	  show_from_dig5 = catx(' ', show_from_dig5,'Total');
call define(_col_,'style','style={fontstyle=italic fontsize=8pt fontweight=bold  }');
end;

	if upcase(_break_) = '_RBREAK_' then do;
	  show_from_dig5 = 'Grand Total';
call define(_col_,'style','style={background=lightyellow}');
end;
  endcomp;

compute region;
  length holdreg $15;
  if region ne ' ' then holdreg = region;
  else if region = ' ' and _break_ = ' ' then region = holdreg;
endcomp;

compute nombre_delegado;
  length holddel $50;
  if nombre_delegado ne ' ' then holddel = nombre_delegado;
  else if nombre_delegado = ' ' and _break_ = ' ' then nombre_delegado = holddel;
endcomp;

compute mkpi;
_c8_=_c7_/_c6_;_c11_=_c10_/_c9_;_c14_=_c13_/_c12_;_c17_=_c16_/_c15_;_c20_=_c19_/_c18_;_c23_=_c22_/_c21_;_c26_=_c25_/_c24_;
_c29_=_c28_/_c27_;_c32_=_c31_/_c30_;_c35_=_c34_/_c33_;_c38_=_c37_/_c36_;_c41_=_c40_/_c39_;_c44_=_c43_/_c42_;_c47_=_c46_/_c45_;_c50_=_c49_/
_c48_;_c53_=_c52_/_c51_;_c56_=_c55_/_c54_;_c59_=_c58_/_c57_;_c62_=_c61_/_c60_;_c65_=_c64_/_c63_;_c68_=_c67_/_c66_;_c71_=_c70_/_c69_;
endcomp;

rbreak after / summarize ul ol;

run;

  ods excel close;