topic Re: proc tabulate to calculate-weighted avg,sum,N,PCT in SAS Programming

proc tabulate to calculate-weighted avg,sum,N,PCT

Ronein — Tue, 14 Aug 2018 10:49:19 GMT

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;

Re: proc tabulate to calculate-weighted avg,sum,N,PCT

ChrisNZ — Tue, 14 Aug 2018 12:05:08 GMT

1. You can only weigh everything or nothing in one proc tabulate step

2. Show the expected output

3. Why not use SQL?

Re: proc tabulate to calculate-weighted avg,sum,N,PCT

PaigeMiller — Tue, 14 Aug 2018 12:48:38 GMT

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;

Re: proc tabulate to calculate-weighted avg,sum,N,PCT

ballardw — Tue, 14 Aug 2018 15:01:18 GMT

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