topic Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE in ODS and Base Reporting

PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

Cello23 — Sun, 08 Jul 2018 12:46:20 GMT

Hi,

i have this proc report:

proc report nowd data=ATM_EXPO.TOTALE_EXPORT_RE_T_&TERM_YM ; 
COLUMN    
  
 PERIMETRO    
 CLUSTER     
 tipo_terminale_    
 N_TERM    
 N_prelievi_medi 
 Importo_prelievi_medio
 Delta_anno_imp_prel 
 
 ORDINE 

 
 Media_trim_LDS_prel    
 Delta_anno_prec_LDS_prel    
 N_TERM_VER 
 N_versamenti_medi   
 Importo_versamenti_medio  
 Delta_anno_imp_verS
 
 Media_trim_LDS_ver_as    
 Delta_anno_prec_ver_as    
 Media_trim_LDS_ver_co    
 Delta_anno_prec_ver_co    
;



DEFINE PERIMETRO /GROUP 'Perimetro';
DEFINE  ORDINE/  NOPRINT ORDER=DATA;
DEFINE CLUSTER  /GROUP ORDER=DATA 'Cluster';
DEFINE  tipo_terminale_  /GROUP ORDER=DATA 'Tipo terminale';

DEFINE  N_TERM /analysis SUM 'Numero ATM' ;
DEFINE N_prelievi_medi  / analysis mean 'Numero prelievi per ATM' ;
DEFINE Importo_prelievi_medio / analysis mean 'Imp.prel. per ATM' ; 
define Delta_anno_imp_prel / analysis mean 'Delta' style(column)={tagattr='format:###0.00%'};  

DEFINE Media_trim_LDS_prel / analysis mean 'LDS prelievo ultimo trimeste' style(column)={tagattr='format:###0.00%'};    
DEFINE  Delta_anno_prec_LDS_prel     / analysis mean 'Delta trimestre anno corr. vs prec.'  style(column)={tagattr='format:###0.00%'}; 
DEFINE N_TERM_VER /analysis SUM 'Numero ATM di versamento' ;  

DEFINE  N_versamenti_medi   / analysis mean 'Numero versamenti per ATM' ;
DEFINE Importo_versamenti_medio / analysis mean 'Imp. versato per ATM' ;  
DEFINE Delta_anno_imp_verS / analysis mean 'Delta' style(column)={tagattr='format:###0.00%'};  

DEFINE  Media_trim_LDS_ver_as  / analysis mean 'LDS ver.ass. ultimo trimestre' style(column)={tagattr='format:###0.00%'};     
DEFINE  Delta_anno_prec_ver_as / analysis mean 'Delta trimestre anno corr. vs prec.' style(column)={tagattr='format:###0.00%'};     
DEFINE  Media_trim_LDS_ver_co  / analysis mean 'LDS ver.cont. ultimo trimestre' style(column)={tagattr='format:###0.00%'};  
DEFINE  Delta_anno_prec_ver_co   / analysis mean 'Delta trimestre anno corr. vs prec.' style(column)={tagattr='format:###0.00%'};
 
 
break after  CLUSTER  / suppress summarize dol skip;
 
compute after CLUSTER;
tipo_terminale_ = "SubTotale" ;
if CLUSTER = "Totale Region"  then call define (_row_, 'style', 'style=[background=lightgreen]');
call define (_row_, 'style', 'style=[background=lightgray]');

endcomp;

compute cluster;
if CLUSTER = "Totale Region" then call define (_row_, 'style', 'style=[background=lightgreen]');
if CLUSTER = "ITALY"  then call define (_row_, 'style', 'style=[background=YELLOW]');
endcomp;

run;

How can i calculate the weighted average with variable “NUMERO ATM’ (1.107,2.092,537,1.796) ??

Thank's so much

Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

PaigeMiller — Sun, 08 Jul 2018 14:13:32 GMT

Use PROC SUMMARY, compute the weighted average there (and all other needed statistics), then create the PROC REPORT code to display it.

Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

BrunoMueller — Tue, 17 Jul 2018 12:15:01 GMT

Proc REPORT has support for the WEIGHT statement. See sample code below. Maybe with this you can get what you need.

proc means data=sashelp.cars sum mean maxdec=0;
  class type;
  var invoice;
  weight Cylinders;
run;

proc report data=sashelp.cars;
  column type invoice=invsum invoice=invavg;
  define type / group;
  define invsum / analysis sum format=comma12. "invsum";
  define invavg / analysis mean format=comma12. "invavg";
  weight Cylinders;
run;

Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

Ronein — Wed, 11 Nov 2020 08:35:46 GMT

I saw this code and tried it but it didnt work

Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

Ronein — Wed, 11 Nov 2020 08:37:31 GMT

data have;
input Customer_ID Team $ Loan_Sum  interest_rate;
cards;
111 A 100 2
333 A 200 3
888 A 300 4
222 A 400 5
111 B 300 6
777 B 400 2
333 B 100 3
;
run;
proc report data=have;
column Team  Loan_Sum=x  Loan_Sum=XX  Customer_ID  interest_rate;
  define Team / group;
  define x / analysis sum format=comma12. "Loan_Sum";/*Wrong result!!*/
  define Customer_ID / analysis N format=comma12. "Nr_Loans";
  define interest_rate / analysis mean format=comma12.3 "Weighted_Avg_interest_rate";
  weight XX;
run;
ERROR: Variable XX not found.

Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

BrunoMueller — Wed, 11 Nov 2020 10:14:35 GMT

The weight variable must be a variable in your input dataset

Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

Ronein — Wed, 11 Nov 2020 10:36:52 GMT

The sum is still not correct.

I even don't understand what values are displayed in sum column

data have;
input Customer_ID Team $ Loan_Sum  interest_rate;
cards;
111 A 100 2
333 A 200 3
888 A 300 4
222 A 400 5
111 B 300 6
777 B 400 2
333 B 100 3
;
run;

proc report data=have;
column Team  Loan_Sum Loan_Sum=x  Customer_ID  interest_rate;
  define Team / group;
  define x / analysis sum format=comma12. "Loan_Sum";/*Wrong result!!*/
  define Customer_ID / analysis N format=comma12. "Nr_Loans";
  define interest_rate / analysis mean format=comma12.3 "Weighted_Avg_interest_rate";
  weight Loan_Sum;
run;

Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

PaigeMiller — Wed, 11 Nov 2020 11:03:32 GMT

You are weighting Loan_Sum by itself. (Essentially squaring loan_sum and then adding up those squares). Probably not what you wanted to do, but its not clear to me what you did want. Show us the desired output.

Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

Ronein — Wed, 11 Nov 2020 12:00:09 GMT

Thank you.
I want to calculate weights average of interest rate.
(It is average of interest rate and the weights are sum of loans)
For Team A the value will be calculated by
(2)(100/1000)+(3)(200/1000)+(4)(300/1000)+(5)(400/1000)=4
For Team B the value will be calculated by
(6)(300/800)+(2)(400/800)+(3)(100/800)=3.625

Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

Kurt_Bremser — Wed, 11 Nov 2020 12:41:35 GMT

As in PROC SUMMARY, do not use a global WEIGHT. Read the documentation of the DEFINE statement.

Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

BrunoMueller — Wed, 11 Nov 2020 13:57:55 GMT

Have a look at the following example. To use a weight on a variable, it has to be a variable in the input data set. So I created a second variable load_sum2 that has the same content as loan_sum.

Both variables are used in the report using an alias ls and ls2. LS2 is using the weight= option. This gives us the two different totals plain and weighted.

We then have a third computed column that uses the two LS... values to computed the number you are looking for.

In the DEFINE statement we can use the NOPRINT option to suppress the printing.

Give it a try:

data have;
  input Customer_ID Team $ Loan_Sum  interest_rate;
  Loan_Sum2 = Loan_Sum;
  cards;
111 A 100 2
333 A 200 3
888 A 300 4
222 A 400 5
111 B 300 6
777 B 400 2
333 B 100 3
;

proc report data=have;
  column Team  Loan_Sum=ls loan_sum2=ls2 ls_wgt Customer_ID  interest_rate;
  define Team / group;
  define ls / analysis sum format=comma12. "Loan SUM";
  define ls2 / analysis sum weight=interest_rate  format=comma12. "Loan SUM weight" /* noprint */ ;
  define ls_wgt / computed format=comma10.3 ;
  define Customer_ID / analysis N format=comma12. "Nr_Loans";
  define interest_rate / analysis mean format=comma12.3 "Weighted_Avg_interest_rate";

  compute ls_wgt;
    ls_wgt = ls2 / ls;
  endcomp;
  ;
run;

Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

Ronein — Wed, 11 Nov 2020 16:52:43 GMT

Thank you!

Your result is 100% but logically I don't understand.
The weight is the sum_loan and not the interest rate.
We want Weighted Average of interest rate (Weights are the sum_loan).
Why did you put the interest as weights and not sum_loan?

Do you understand my point?

Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

Kurt_Bremser — Wed, 11 Nov 2020 17:40:57 GMT

You can get this easier with a WEIGHT= option:

proc report data=have;
column Team Loan_Sum Customer_ID interest_rate;
define Team / group;
define loan_sum / analysis sum;
define Customer_ID / analysis N format=comma12. "Nr_Loans";
define interest_rate / analysis mean weight=loan_sum format=comma12.3 "Weighted_Avg_interest_rate";
run;

Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

Ronein — Thu, 12 Nov 2020 05:36:21 GMT

Hello

I want to ask about the following statement

define ls2 / analysis sum weight=interest_rate format=comma12. ;

Is it performing SUM of multiplications of Loan_Sum and interest_rate?

If yes,will it equivalent to write

define interest_rate / analysis sum weight=ls2 format=comma12. ;

data have;
  input Customer_ID Team $ Loan_Sum  interest_rate;
  Loan_Sum2 = Loan_Sum;
  cards;
111 A 100 2
333 A 200 3
888 A 300 4
222 A 400 5
111 B 300 6
777 B 400 2
333 B 100 3
;
proc report data=have;
  column Team  Loan_Sum=ls loan_sum2=ls2 ls_wgt Customer_ID  interest_rate;
  define Team / group;
  define ls / analysis sum format=comma12. "Loan SUM";
  define ls2 / analysis sum weight=interest_rate  format=comma12. "Loan SUM weight" /* noprint */ ;
  define ls_wgt / computed format=comma10.3 ;
  define Customer_ID / analysis N format=comma12. "Nr_Loans";
  define interest_rate / analysis mean format=comma12.3 "Weighted_Avg_interest_rate";
  compute ls_wgt;
    ls_wgt = ls2 / ls;
  endcomp;
  ;
run;

Re: PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

BrunoMueller — Thu, 12 Nov 2020 07:31:01 GMT

Yes weight= will multiply the original value with the weight value.

You can not use an alias (ls2) in the weight= option it has to be a proper variable.

The example provided by @Kurt_Bremser does exactly what you need. Please note it is using the MEAN statistic, as this is what you need.