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

Options

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

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

Posted 08-15-2023 02:52 AM
(242 views)

Hello

I would like to calculate:

Weighted average of interest on loans (The weight is on loan amount)

Number of loans

Number of customers

Sum of loans

Here are some ways to calculate it with one-way-distribution or two-way-distribution.

I have some questions please:

1-Using proc tabulate to calculate One_Way_dist

How can I add to calculation also -Nr_Loans,Loan_Sum,Nr_Customers

2-Using proc summary to calculate One_Way_dist

How can I add to calculation also -Nr_Customers

3-Using proc Report to calculate One_Way_dist

Why Loan_Sum is wrong calculated??

How to calculate also Nr_Customers??

4-Using proc Report to calculate Tw0-Way-dist, how to do it?

```
/*********Weighted average**************/
/*********Weighted average**************/
/*********Weighted average**************/
Data have;
Input Customer_ID CAT1 $ CAT2 $ amnt interest;
cards;
1 a T 100 4
2 a T 200 5
3 a F 300 6
4 a F 400 4
5 a T 500 4
6 a T 600 8
7 a F 700 10
8 b F 800 11
9 b F 900 12
10 b T 100 5
11 b T 200 6
12 b T 300 8
13 b T 400 3
14 b T 500 4
15 b F 600 7
;
Run;
/*********One-Way-Dist-W_Avg,nr_Loans,nr_Customers,Sum_Loans***********/
/*********One-Way-Dist-W_Avg,nr_Loans,nr_Customers,Sum_Loans***********/
/*********One-Way-Dist-W_Avg,nr_Loans,nr_Customers,Sum_Loans***********/
PROC SQL;
select CAT1,
count(*) as Nr_Loans format=comma12.,
count(distinct Customer_ID) as nr_Customers format=comma12.,
sum(amnt) as Loan_Sum,
sum(interest*amnt)/sum(amnt) as w_avg_interest format=8.4
from have
group by CAT1
;
QUIT;
/*********One-Way-Dist***********/
/*********One-Way-Dist***********/
/*********One-Way-Dist***********/
/***Question-How to calculate also Nr_Loans,Loan_Sum,Nr_Customers???*****/
proc tabulate data=have format=8.4 MISSING;
class CAT1;
var interest;
weight amnt;
TABLE CAT1='' ALL,interest=''*MEAN='W_Avg_interest'/box='CAT1';
run;
/*********One-Way-Dist-W_Avg,nr_Loans,Loan_Sum***********/
/*********One-Way-Dist-W_Avg,nr_Loans,Loan_Sum***********/
/*********One-Way-Dist-W_Avg,nr_Loans,Loan_Sum***********/
/***Question---How to calculate also Nr_Customers???****/
proc sort data=have;
by CAT1;
Run;
proc summary data=have;
by CAT1;
var interest / weight=amnt;
var amnt;
output out=want(drop=_:)
mean(interest)=w_avg_interest
N(amnt)=nr_obs
sum(amnt)=loan_sum
;
run;
/*********One-Way-Dist-W_Avg,nr_Loans,Sum_Loans---Wrong results for Sum_Loans!!!***********/
/*********One-Way-Dist-W_Avg,nr_Loans,Sum_Loans---Wrong results for Sum_Loans!!!***********/
/*********One-Way-Dist-W_Avg,nr_Loans,Sum_Loans---Wrong results for Sum_Loans!!!***********/
/***Question---Why Loan_Sum is wrong calcukated????*****/
/***Question---How to calculate also Nr_Customers???****/
proc report data=have;
column CAT1 amnt Customer_ID interest;
define CAT1 / group;
define amnt / analysis sum format=comma12. "Loan_Sum";/*Wrong result!!*/
define Customer_ID / analysis N format=comma12. "Nr_Loans";
define interest / analysis mean format=8.4 "w_avg_interest";
weight amnt;
run;
/*********Two-Way-Dist***********/
/*********Two-Way-Dist***********/
/*********Two-Way-Dist***********/
proc tabulate data=have format=8.4 MISSING;
class CAT1 CAT2;
var interest;
weight amnt;
TABLE interest=''*MEAN='' , CAT1='' ALL,CAT2 ALL/box='CAT1';
run;
/**Question----How can I calculate it via proc report???/
```

1 REPLY 1

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

When you say something is "wrong" as in

/*********One-Way-Dist-W_Avg,nr_Loans,Sum_Loans---Wrong results for Sum_Loans!!!***********/ /*********One-Way-Dist-W_Avg,nr_Loans,Sum_Loans---Wrong results for Sum_Loans!!!***********/ /*********One-Way-Dist-W_Avg,nr_Loans,Sum_Loans---Wrong results for Sum_Loans!!!***********/ /***Question---Why Loan_Sum is wrong calcukated????*****/

You need to provide what YOU think is the correct answer and how you derive it.

When you ask:

/***Question-How to calculate also Nr_Loans,Loan_Sum,Nr_Customers???*****/ proc tabulate data=have format=8.4 MISSING; class CAT1; var interest; weight amnt; TABLE CAT1='' ALL,interest=''*MEAN='W_Avg_interest'/box='CAT1'; run;

You need to provide SOMETHING as a Class variable to count for proc tabulate to get an N. Like Customer_id. How does Nr_customers vary from Nr_loans? From the example data I would think it the same. But are there supposed to counts within some group? Need to specify.

You need a VAR variable to SUM. Same comment related to grouping as well. If you do not want a weighted sum then you need to use a separate call to proc Tabulate (or means or summary or report) that does NOT use the Weight statement.

So for a "pretty" table you may need to calculate some things separately, combine them and display the combined result.

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.