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;
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
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.
Please have a look at the documentation of the weight-statement in proc summary.
The weight average is working well but the sum not
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;
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
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.
Just to be a friendly-minded voice in the crowd: Read the documentation 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.