topic Re: Part 2 - How to add the dollars amount to previous logic that count by row groups in SAS Programming

Part 2 - How to add the dollars amount to previous logic that count by row groups

marcus7w — Wed, 22 Jun 2022 03:59:26 GMT

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.

Re: Part 2 - How to add the dollars amount to previous logic that count by row groups

PaigeMiller — Wed, 22 Jun 2022 10:25:51 GMT

I don't see how the final table WANT is computed from the input data. Please explain the steps.

Re: Part 2 - How to add the dollars amount to previous logic that count by row groups

marcus7w — Wed, 22 Jun 2022 10:55:24 GMT

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.

Re: Part 2 - How to add the dollars amount to previous logic that count by row groups

PaigeMiller — Wed, 22 Jun 2022 11:34:06 GMT

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;

Re: Part 2 - How to add the dollars amount to previous logic that count by row groups

marcus7w — Wed, 22 Jun 2022 12:06:58 GMT

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