Weighted average

Ronein — Tue, 15 Aug 2023 06:52:44 GMT

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???/

Re: Weighted average

ballardw — Tue, 15 Aug 2023 14:41:49 GMT

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.

topic Weighted average in SAS Programming

Weighted average

Re: Weighted average