Weighted average by group

[Ronein]

hello

I have loans information.

Each row contain information about loan that a customer received.

I want to calculate for each team the following 3 statistics:

1-Number of loans

2-Sum of loans

3-Weighted average of interest rate

I have tried to use proc summary but result of sum of loans is not correct.

What is the way to get correct results via proc summary?

What is the way to perform it via proc tabulate?

What is the way to perform it via proc report?

I want also to add a summary row that contain information for all teams together.

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;


data have2;
set have;
Loan_Sum2=Loan_Sum;
Run;

proc summary data=have2;
by Team;
var interest_rate Loan_Sum2;
weight Loan_Sum;
output out=want(drop=_:)
mean(interest_rate)=weighted_avg_interest_rate
N(Loan_Sum2)=Nr_Obs 
sum(Loan_Sum)=Loan_Sum ;
Run;

[andreas_lds]

Please have a look at the documentation of the weight-statement in proc summary.

[Ronein]

The weight average is working well but the sum not

[Ronein]

Both proc summary and proc report are not working well  for sum calculation.

May anyone help please?

This is the target of this forum I think, Thanks Dave

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;
/*Way1-proc sql*/
PROC SQL;
	select  Team,
            count(*) as Nr_Loans,
            sum(Loan_Sum) as Loan_Sum,
            sum(interest_rate*Loan_Sum)/sum(Loan_Sum) as Weighted_Avg_interest_rate 
	from  have
	group by Team
;
QUIT;
/*Way2-proc report*/
proc report data=have;
column Team  Loan_Sum=x  Loan_Sum  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;
/*Way3-proc summary*/
data have2;
set have;
sum_halv2=sum_halv;
Run;
proc summary data=have2;
by Team;
var interest_rate;
weight Loan_Sum;
output out=want(drop=_:)
N(Customer_ID)=Nr_Loans
sum(Loan_Sum)=Loan_Sum /*Wrong result!!*/
mean(interest_rate)=Weighted_Avg_interest_rate
;
Run;

[Kurt_Bremser · Accepted solution]

Once again, I have to make a clear statement for Maxim 1:

READ THE DOCUMENTATION.

I mean it.

Really.

Yes.

You want to use PROC SUMMARY/MEANS, with which you are not very familiar, so your first step is to study the documentation.

The VAR statement in these procedures has these important messages for you:

Syntax

VAR variable(s) </ WEIGHT=weight-variable>;

So you can define a weight variable without a WEIGHT statement, and make it specific for a variable.

And this:

Tip:	You can use multiple VAR statements.

So you can have one or more variables weighted, and one or more variables not weighted.

Eureka! We can now write the code like this:

proc summary data=have;
by team;
var interest_rate / weight=loan_sum;
var loan_sum;
output
  out=want(drop=_:)
  mean(interest_rate)=weighted_avg_interest_rate
  N(loan_sum)=nr_obs 
  sum(loan_sum)=loan_sum
;
run;

and that's it.

Please take note that I am no expert for these procedures at all; all I did was to study the documentation and apply the knowledge gained from that. You can do that also. Everyone can.

[PeterClemmensen]

Just to be a friendly-minded voice in the crowd: Read the documentation 🙂