Hello
I have a row data of loans taken by customers.
I need to calculate for each score (category variable)the following 5 measurements:
1-weighted average of interest (weight by sum of loan)
2-sum of loan
3-number of loans
4-percent from total of sum of loans
5-percent from total of number of loans
I want to use proc tabulate because in proc means we cannot calculate percent from total in one step
I would like to see please proc tabulate code that calculate all 5 measurements
thanks
Jowl
data Loans_tbl1806;
input customer score $ sum_loan interest;
cards;
1 a 100 5
2 a 200 4
3 a 300 4
4 b 400 3.5
5 b 500 2.8
6 c 100 5
7 c 200 4
8 c 300 4
9 c 400 3.5
10 c 1500 2.8
;
run;
1. You can only weigh everything or nothing in one proc tabulate step
2. Show the expected output
3. Why not use SQL?
Sometimes, you can get the job done in less programming time if you don't insist the result has to come from one PROC or one DATA step. If you are willing to use two (or more) PROCs or DATA steps, then the answer comes relatively quickly.
UNTESTED CODE
proc summary data=loans_tbl1806;
class score;
var interest/weight=sum_loan;
var sum_loan;
output out=_stats_ mean(interest)=mean_interest n(sum_loan)=n_loan sum(sum_loan)=;
run;
data want;
if _n_=1 then set _stats_(where=(_type_=0) rename=(sum_loan=total_sum_loan n_loan=total_n_loan)
drop=mean_interest score);
set _stats_(where=(_type_=1));
percent_of_sum_loan=sum_loan/total_sum_loan;
percent_of_n_loan=n_loan/total_n_loan;
run;
@Ronein wrote:
Hello
I have a row data of loans taken by customers.
I need to calculate for each score (category variable)the following 5 measurements:
1-weighted average of interest (weight by sum of loan)
2-sum of loan
3-number of loans
4-percent from total of sum of loans
5-percent from total of number of loans
I want to use proc tabulate because in proc means we cannot calculate percent from total in one step
I would like to see please proc tabulate code that calculate all 5 measurements
thanks
Jowl
data Loans_tbl1806; input customer score $ sum_loan interest; cards; 1 a 100 5 2 a 200 4 3 a 300 4 4 b 400 3.5 5 b 500 2.8 6 c 100 5 7 c 200 4 8 c 300 4 9 c 400 3.5 10 c 1500 2.8 ; run;
While proc tabulate can create some percentages of sums or ns of other variables anything involving a "weight" would apply to all calculations and what ever you may mean by "weight by sum of loan" is likely not possible. In face, I am not sure exactly what you may mean by that "weight by sum of loan" and would need to see a worked out example by hand to tell what is needed.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.