Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Weighted average by group

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 11-11-2020 01:21 AM
(2310 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.*

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

The weight average is working well but the sum not

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.*

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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!

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.