@Ronein wrote:
Hello
I work in a bank as a credit risk analyst.
I want to talk about a monthly loans data set .
In this table there are new loans that customers takes from the bank.
As you can see in the data it might happen that a customer takes more than one loan.
The target is to calculate 5 aggraegative fields:
"No_Loans" is the number of loans (number of rows in the table)
"No_Customers" is the number of customers who took a loan
"Sum_Loan" is the sum of loans (in us dollars)
"Weighted_interest" is the weighted interest average (Weighed by sum of loans)
"Pct_Sum_Loan" is pct of sum of loans in each category
I know to calculate these fields in a long process.
My question is If someone have a suggestion how to calculate it in a shorter code?
Data tbl1;
input ID 1
Loan_ID 3
Sum_Loan 5-8
Interest
date_Loan :ddmmyy10.
date_Finish :ddmmyy10.
Loan_Model
grade;
format date_Loan date_Finish date9.;
Cards;
1 1 100 2.4 01/06/2018 01/06/2023 3312 4
1 2 200 2.6 02/06/2018 02/06/2021 3312 5
1 3 300 2.3 05/06/2018 05/06/2025 3313 5
2 4 400 3.1 05/06/2018 05/06/2020 3312 6
2 5 500 2.9 05/06/2018 05/06/2021 3313 5
;
run;
PROC SQL;
create table Output1 as
select grade,
count(*) as No_Loans,
count(distinct ID) as No_Customers,
sum(Sum_Loan) as Sum_Loan
from tbl1
group by grade
;
QUIT;
/*Calculate weighted average of interest by sum Loans*/
PROC SQL;
create table Output2 as
select grade,
sum(Interest*Sum_Loan)/sum(Sum_Loan) as Weighted_interest
from tbl1
group by grade
;
QUIT;
/*Calculate Percent Tamhil*/
PROC SQL;
create table Output3 as
select grade,
Sum_Loan/sum(Sum_Loan) as Pct_Sum_Loan format=percent9.1
from Output1
;
QUIT;
PROC SQL;
create table Output4 as
select a.*,b.Weighted_interest,c.Pct_Sum_Loan
from Output1 as a
left join Output2 as b
on a.grade=b.grade
left join Output3 as c
on a.grade=c.grade
;
QUIT;
PROC SQL;
create table Output5 as
select sum(No_Loans) as No_Loans,
sum(No_Customers) as No_Customers,
sum(Sum_Loan) as Sum_Loan,
sum(Weighted_interest*Pct_Sum_Loan) as Weighted_interest,
sum(Pct_Sum_Loan) as Pct_Sum_Loan format=percent9.1
from Output4
;
QUIT;
Data FinalOutput(drop=grade rename=(grade_Char=grade));
Retain grade_Char;
Set Output4 Output5;
length grade_Char $3.;
grade_Char=put(grade,2.);
If grade=. then grade_Char='All';
Run;
Your Topic title involves "How to write more efficient program" but then you specify "how to calculate it in a shorter code". Which are not the same thing. Efficiency could mean "reduced run time", "reduced disk space usage" or "reduced memory usage" or perhaps even "easy to understand and maintain code".
"Shorter code" does not necessarily do any of the above. I have seen some very short code code did some very interesting things. But minor changes to the data "broke" the code.
Your list of requirements does not describe the role of the variable GRADE which is used in multiple places. So we're going to have a hard time with combining "no_loans" "no_customers" if grade affects these counts. What happens when a customer has some loans with different grades?
Additionally a question arises of do you want a Report at the end of this that people read or a data set to feed into another process?
Click image to register for webinar
