cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
acordes
Rhodochrosite | Level 12 acordes
Rhodochrosite | Level 12
Go to Solution

Proc report compute before - across collision

Posted 11-05-2021 05:24 AM (449 views)

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;

img.png

0 Likes
1 ACCEPTED SOLUTION

Accepted Solutions
acordes
Rhodochrosite | Level 12 acordes
Rhodochrosite | Level 12

Re: Proc report compute before - across collision

Posted 11-06-2021 03:23 AM (367 views)  |   In reply to acordes

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;

img.png

View solution in original post

0 Likes
1 REPLY 1
acordes
Rhodochrosite | Level 12 acordes
Rhodochrosite | Level 12

Re: Proc report compute before - across collision

Posted 11-06-2021 03:23 AM (368 views)  |   In reply to acordes

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;

img.png

0 Likes

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • ‎11-05-2021 05:24 AM
  • 450 views
  • 0 likes
  • 1 in conversation