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.
... View more
