BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ronein
Meteorite | Level 14

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;

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

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.

View solution in original post

5 REPLIES 5
andreas_lds
Jade | Level 19

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

Ronein
Meteorite | Level 14

The weight average is working well but the sum not

Ronein
Meteorite | Level 14

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
Super User

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

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
Tourmaline | Level 20

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

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2311 views
  • 3 likes
  • 4 in conversation