proc report compute before

acordes — Wed, 19 Jan 2022 14:57:06 GMT

I'm struggling to sum the 'Ven.' column as compute before from brand.

For example, I want 17788 to appear in every row of the first block for year 2021 so that the KPI column gets calculated as 'Ren.' (correct) divided by 'Ven.'.

for the moment I only share the screenshot while preparing a data set and the code to play with.

proc report data=PUBLIC.ALL_MONTHLY_copy nowd center split="*" out=three  ;
where lowcase(producto) ne "other" and month('month'n) le 6 AND VN_VN IN ('10' '11');
/* by vn_vn channel; */
format 'month'n monyy. vn_vn $vner.;
column from_brand to_brand  year,  (fin pct_fin ren pct_ren mkpi )  kpi ;
define year / 'YTD JUN'  center order=internal across;
define pct_ren / 'Share Ren' computed format=percent9.1;
define pct_fin / 'Share Fin' computed format=percent9.1;
define from_brand / group 'from brand' center;
define to_brand / group 'to brand' center ;
define fin / analysis sum 'Ven.' ;
define ren / analysis sum  'Ren.'   ;
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 from_brand;
	ren_total2019 = _c5_;
	   ren_total2020 = _c10_;
	   ren_total2021 = _c15_;
	fin_total2019 = _c3_;
	   fin_total2020 = _c8_;
	   fin_total2021 = _c13_;
   endcomp;


compute pct_fin;
_c4_=_c3_/fin_total2019;
_c9_=_c8_/fin_total2020;
_c14_=_c13_/fin_total2021;
endcomp;

compute pct_ren;
_c6_=_c5_/ren_total2019;
_c11_=_c10_/ren_total2020;
_c16_=_c15_/ren_total2021;
endcomp;



compute kpi;
kpi = _c12_ - _c7_;
endcomp;

compute mkpi;
_c7_=_c5_/_c3_;_c12_=_c10_/_c8_;_c17_=_c15_/_c13_;
endcomp;

run;

Re: proc report compute before

acordes — Wed, 19 Jan 2022 17:06:10 GMT

replacing the data set by a joined table to do the aggregation does the job.

but I can imagine that a compute before should work as well.

proc cas;
    fedSQL.execDirect / 
        query="create table public.sum22{options replace=true} as 
select a.*, b.fini, put(a.year,4.) as _year 
               from PUBLIC.ALL_MONTHLY_COPY a   join 
(select sum(fin) as fini, from_brand, year from 
PUBLIC.ALL_MONTHLY_COPY where month(mes) <=6 and 
lowcase(producto) <> 'other' AND (VN_VN ='10' or VN_VN = '11') group by from_brand, year 
) b 
on a.from_brand=b.from_brand and a.year=b.year ";
quit;


options missing=0;
proc report data=public.sum22 nowd center split="*" out=three  ;
where lowcase(producto) ne "other" and month('month'n) le 6 AND VN_VN IN ('10' '11');
/* by vn_vn channel; */
format 'month'n monyy. vn_vn $vner.;
column from_brand to_brand  year,  (fini pct_fin ren pct_ren mkpi )  kpi  ;
define year / 'YTD JUN'  center order=internal across;
define pct_ren / 'Share Ren' computed format=percent9.1;
define pct_fin / 'Share Fin' computed format=percent9.1;
define from_brand / group 'from brand' center;
define to_brand / group 'to brand' center ;
define fini / analysis max 'Ven.' ;
define ren / analysis sum  'Ren.'   ;
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 from_brand ;
	ren_total2019 = _c5_;
	   ren_total2020 = _c10_;
	   ren_total2021 = _c15_;
	fin_total2019 = _c3_;
	   fin_total2020 = _c8_;
	   fin_total2021 = _c13_;
   endcomp;


compute pct_fin;
_c4_=_c3_/fin_total2019;
_c9_=_c8_/fin_total2020;
_c14_=_c13_/fin_total2021;
endcomp;

compute pct_ren;
_c6_=_c5_/ren_total2019;
_c11_=_c10_/ren_total2020;
_c16_=_c15_/ren_total2021;
endcomp;



compute kpi;
kpi = _c12_ - _c7_;
endcomp;

compute mkpi;
_c7_=_c5_/_c3_;_c12_=_c10_/_c8_;_c17_=_c15_/_c13_;
endcomp;

run;

topic Re: proc report compute before in SAS Programming

proc report compute before

Re: proc report compute before