Couple weeks ago I asked the Part 1 question
Question Part 1 : if a customer has more than one product at the bank, how many products are they're delinquent on (60 days delq & greater is Bad) and how many Goods. The solution I wanted was the count of the number account id with # of bads and # of good. Below is the solutions.
Question Part 2 - How do I add the sum of dollar amounts for Good$ and Bad$ and add it to the Part 1 results. I want to know the $expourse of potential loss if customer is a bad on other products.
Data Have: | ||||||||
Input | ||||||||
Account ID | Balance | Delinq Days | Product | Good | Bad | Good$ | Bad$ | |
1234 | $10,000 | 30 | Auto | 1 | $10,000 | |||
1234 | $100,000 | 60 | Mortgage | 1 | $100,000 | |||
1234 | $5,000 | 0 | Credit Card | 1 | $5,000 | |||
5555 | $10,000 | 30 | Auto | 1 | $10,000 | |||
5555 | $100,000 | 60 | Mortgage | 1 | $100,000 | |||
5555 | $5,000 | 90 | Credit Card | 1 | $5,000 | |||
7777 | $10,000 | 30 | Auto | 1 | $10,000 | |||
7777 | $100,000 | 60 | Mortgage | 1 | $100,000 | |||
7777 | $5,000 | 90 | Credit Card | 1 | $5,000 | |||
7777 | $5,000 | 90 | Credit Card | 1 | $5,000 | |||
2121 | $10,000 | 30 | Auto | 1 | $10,000 | |||
2121 | $100,000 | 0 | Mortgage | 1 | $100,000 | |||
2121 | $5,000 | 30 | Credit Card | 1 | $5,000 | |||
2121 | $5,000 | 0 | Credit Card | 1 | $5,000 | |||
8888 | $10,000 | 30 | Auto | 1 | $10,000 | |||
8888 | $100,000 | 60 | Mortgage | 1 | $100,000 | |||
8888 | $5,000 | 90 | Credit Card | 1 | $5,000 | |||
8888 | $5,000 | 90 | Credit Card | 1 | $5,000 | |||
Part 1 Code :
proc sql:
create table want_part 1 as select
n_bads,
n_goods,
count(*) as n_account_id
from(
select account_id,
sum(Bad) as n_bads,
sum(Good) as n_goods
from Have
group by Account_id
)
group by n_bads, n_goods;
;
quit;
Part 1 Results
N_Goods | N_BADS | N_Account ID |
1 | 2 | 1 |
1 | 3 | 2 |
2 | 1 | 1 |
4 | 0 | 1 |
Part 2 Want :
N_Goods | N_BADS | sum_Good$ | sum_Bad$ | N_Account ID |
1 | 2 | $10,000 | $105,000 | 1 |
1 | 3 | $20,000 | $220,000 | 2 |
2 | 1 | $15,000 | $100,000 | 1 |
4 | 0 | $120,000 | $0 | 1 |
in this example, I made Account Id 7777 and 8888 identical so that sum(Good$) equal $20,000 and sum(bads$) equal $220,000 and N_account Id equal 2
How do I add sum of the Good$ and sum of the Bad$ to Part 1 Code?
Every attempt I tried, I ended with unique row for the dollar amount instead sum by the groups.
Ok, here is UNTESTED CODE. If you want tested code, the data must be provided via SAS data step code (instructions). The code uses PROC SUMMARY rather than jumping through hoops to obtain SQL code that works. In the WANT data set, the variable _FREQ_ contains the value of what you are calling N_ACCOUNTID
proc summary data=have nway;
class account_id;
var good bad good_dollars bad_dollars;
output out=stats sum=;
run;
proc summary data=stats nway;
class good bad;
var good_dollars bad_dollars;
output out=want sum=;
run;
I don't see how the final table WANT is computed from the input data. Please explain the steps.
Hi PaigeMiller.
I asking how to write the logic to compute the WANT (part).
For example on the Input table, let me explain by using Account ID 7777 and 8888 for this discussion. Both account id has #count grouping of 1 Bad (delinquncy) and 3 Good (no delinquency). For the WANT, I need help in how to code the sum of Bad$(dollar amount) and sum of Good$ (dollar amount) for each grouping of Bad and Good combination.. Since Account ID 7777 and 8888 has same incident grouping of bad (1) and good (3), that results in count of 2 for n_account_id on the Want table and for Bad$ and Good$ dollars amount result would be the sum of account_id 7777 and 8888 would result in $20,000 (Bads$) and $220, 000 (Good$) (the result of discussion example is the second row of the WANT table)
The intent of my project, if a customer has deliquency(bad) in one product, that person is most like to go likely delinquency in other loan products that are in Good standing. So the question what is the Risk Exposure? For the customers with more than loan product, How many accounts has Bad vs Good? Part 1 code answer this question. Part 2 question - What is the Bad$ vs Good$ dollars amount that goes along with Part 1 results.
Ok, here is UNTESTED CODE. If you want tested code, the data must be provided via SAS data step code (instructions). The code uses PROC SUMMARY rather than jumping through hoops to obtain SQL code that works. In the WANT data set, the variable _FREQ_ contains the value of what you are calling N_ACCOUNTID
proc summary data=have nway;
class account_id;
var good bad good_dollars bad_dollars;
output out=stats sum=;
run;
proc summary data=stats nway;
class good bad;
var good_dollars bad_dollars;
output out=want sum=;
run;
Good Morning@PaigeMiller
Thanks for the instructions. SAS is installed my work laptop and this website is block, so I'm using my personal laptop without SAS installed to ask the question. The untested code work perfect. Thank you so much for time and helping me
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.