PROC REPORT - SUMMUARIZE WITH WEIGHTED AVERAGE

[Cello23]

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

[PaigeMiller]

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

[BrunoMueller]

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;

[Ronein]

I saw this code and tried it but it didnt work

[Ronein]

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.

[BrunoMueller]

The weight variable must be a variable in your input dataset

[Ronein]

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;

[PaigeMiller]

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.

[Ronein]

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

[BrunoMueller]

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;

[Ronein]

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?

[Kurt_Bremser]

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;

[Kurt_Bremser]

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

[Ronein]

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;

[BrunoMueller]

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.