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