Hello
I need to calculate some summary statistics .
I know to do it multiple steps.
My question is how to do it in one step with the following procedures : proc tabulate or Proc Report or Proc sql or proc summary.
In the calculation there are 5 required summary fields:
Number of loans
Number of customers
sum of loans
Average interest
Weighted Average interest (The weight is by sum of loan)
Data Loans;
format date date9.;
input ID date:date9. Sum_Loan interest;
cards;
1 10Jan2019 10 2.1
1 13Jan2019 20 2.2
1 18Feb2019 30 1.9
2 10Jan2019 40 3.5
3 19Jan2019 50 3.7
4 21Feb2019 60 2.9
5 08Jan2019 70 8.0
5 26Feb2019 80 7.0
;
run;
/*Required calculations are:
Number of loans:8
Number of customers:5
Sum Loans: 360
Average interest:3.91
Weighted Average interest:4.83*/
PROC SQL;
create table tbl1 as
select count(*) as Number_of_loans
from Loans
;
QUIT;
PROC SQL;
create table tbl2 as
select count(distinct ID ) as Number_of_customers
from Loans
;
QUIT;
PROC SQL;
create table tbl3 as
select sum(Sum_Loan) as Total_Sum_Loans
from Loans
;
QUIT;
PROC SQL;
create table tbl4 as
select avg(interest) as avg_interest
from Loans
;
QUIT;
PROC SQL;
create table tbl4 as
select avg(interest) as avg_interest
from Loans
;
QUIT;
proc sql;
create table tbl5_a as
select a.*, b.Total_Sum_Loans,a.Sum_Loan/b.Total_Sum_Loans as Weight
from Loans as a, tbl3 as b
;
quit;
PROC SQL;
create table tbl5_b as
select sum(Weight*interest) as weighted_avg_interest
from tbl5_a
;
QUIT;
I am using a few steps in order to get the desired summary table but I don't know how to do it in one step
/*Way2*/
/*Way2*/
/*Way2*/
/*Way2*/
/*Way2*/
PROC TABULATE data=Loans out=bbb;
VAR ID Sum_Loan interest;
TABLE ID*N Sum_Loan*SUM interest*mean;
RUN;
PROC SQL;
create table ccc as
select sum(Sum_Loan) as Total_Sum_Loans
from Loans
;
QUIT;
proc sql;
create table ddd as
select a.*, b.Total_Sum_Loans,a.Sum_Loan/b.Total_Sum_Loans as Weight
from Loans as a, ccc as b
;
quit;
PROC TABULATE data=ddd out=eee(rename=(interest_Mean=Weighted_interest_Mean));
VAR Sum_Loan interest;
weight Weight;
TABLE interest*MEAN;
RUN;
PROC SQL;
create table fff as
select count(distinct ID) as No_of_customers
from Loans
;
QUIT;
Data Result(drop=_type_ _Page_ _table_);
Merge bbb eee fff;
run;
PROC SUMMARY is such a fundamental tool that everyone should know how to use it.
Data Loans;
format date date9.;
input ID date:date9. Sum_Loan interest;
interest1=interest;
cards;
1 10Jan2019 10 2.1
1 13Jan2019 20 2.2
1 18Feb2019 30 1.9
2 10Jan2019 40 3.5
3 19Jan2019 50 3.7
4 21Feb2019 60 2.9
5 08Jan2019 70 8.0
5 26Feb2019 80 7.0
;
run;
proc summary data=loans;
var id sum_loan interest;
var interest1/weight=sum_loan;
output out=_stats_ max(id)=n_customers n(id)=nloans sum(sum_loan)=sum_loans
mean(interest interest1)=mean_interest wgt_mean_interest;
run;
Adding, in the case of computing a weighted mean interest rate, and in the presence of missing values in either the interest rate or in the weight variable, PROC SQL approaches will give the wrong answer. PROC SUMMARY gives the correct answer.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.