topic Re: Apply roll rate multiplier to project future 24 month buckets on a roll forward basis in SAS Programming

Apply roll rate multiplier to project future 24 month buckets on a roll forward basis

koya — Thu, 16 Jul 2020 19:08:01 GMT

Hi,

I have the June'20 month end balance (ME_BALANCE) for each delinquency bucket. And also the historical roll rate calculated to determine the roll % (ROLL_RATE) to next months delinquency bucket (DLQ_STATUS_NEXT_MONTH). I need to formulate a macro to calculate balances to July'20 delinquency buckets using June'20 balances and balances to August'20 delinquency buckets based on July'20 balances and so on for next 24 months. Is there any existing macro or procedure for this. Thanks

ME_BALANCE	DLQ_STATUS
$100	1
$90	2
$80	3
$70	4
$60	5
$50	6
$40	7

DLQ_STATUS	DLQ_STATUS_NEXT_MONTH	ROLL_RATE
1	1	97.99%
1	2	0.64%
1	3	0.00%
1	4	0.00%
1	5	0.00%
1	6	0.00%
1	7	0.00%
1	8	0.00%
1	9	0.00%
1	10	1.36%
2	1	30.45%
2	2	49.72%
2	3	18.16%
2	4	0.09%
2	5	0.04%
2	6	0.04%
2	7	0.10%
2	8	0.07%
2	9	0.08%
2	10	1.52%
3	1	13.57%
3	2	16.62%
3	3	37.65%
3	4	28.25%
3	5	0.15%
3	6	0.10%
3	7	0.29%
3	8	2.21%
3	9	0.16%
3	10	1.55%
4	1	16.21%
4	2	6.62%
4	3	12.54%
4	4	21.98%
4	5	16.74%
4	6	0.43%
4	7	0.45%
4	8	22.58%
4	9	0.93%
4	10	2.46%
5	1	18.64%
5	2	3.39%
5	3	3.23%
5	4	5.51%
5	5	16.59%
5	6	33.86%
5	7	0.58%
5	8	17.51%
5	9	2.54%
5	10	2.43%
6	1	29.97%
6	2	8.57%
6	3	13.83%
6	4	7.11%
6	5	7.54%
6	6	17.04%
6	7	16.64%
6	8	57.72%
6	9	5.35%
6	10	7.39%
7	1	2.62%
7	2	0.36%
7	3	0.11%
7	4	0.06%
7	5	0.05%
7	6	0.14%
7	7	0.14%
7	8	94.10%
7	9	1.43%
7	10	1.42%

Expected output (only showing 3 future months):

DLQ_STATUS	ME_BALANCE	Month
1	$100	Jun'20
2	$90	Jun'20
3	$80	Jun'20
4	$70	Jun'20
5	$60	Jun'20
6	$50	Jun'20
7	$40	Jun'20
8	$30	Jun'20
1	$100	Jul'20
2	$90	Jul'20
3	$80	Jul'20
4	$70	Jul'20
5	$60	Jul'20
6	$50	Jul'20
7	$40	Jul'20
8	$30	Jul'20
1	$100	Aug'20
2	$90	Aug'20
3	$80	Aug'20
4	$70	Aug'20
5	$60	Aug'20
6	$50	Aug'20
7	$40	Aug'20
8	$30	Aug'20

Did the math in excel using MMULT function, see the results below. hoping to get this in SAS

	Roll rate matrix	Next month delinquency
		1	2	3	4	5	6	7	8
Current month delinquency	1	0.9799	0.0064	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000
	2	0.3045	0.4972	0.1816	0.0009	0.0004	0.0008	0.0007	0.0008
	3	0.1357	0.1662	0.3765	0.2825	0.0015	0.0017	0.0221	0.0016
	4	0.1621	0.0662	0.1254	0.2198	0.1674	0.0044	0.2258	0.0093
	5	0.1864	0.0339	0.0323	0.0551	0.1659	0.3211	0.1751	0.0254
	6	0.2997	0.0857	0.1383	0.0711	0.0754	0.1687	0.5772	0.0535
	7	0.0262	0.0036	0.0011	0.0006	0.0005	0.0014	0.9410	0.0143
	8	0.0023	0.0011	0.0000	0.0000	0.0000	0.0000	0.0006	0.9994

	Jun'20	$100	$90	$80	$70	$60	$50	$40	$30
	Jul'20	$ 174.89	$ 69.81	$ 64.14	$ 44.96	$ 25.62	$ 28.27	$ 94.66	$ 35.61
	Aug'20	$ 224.43	$ 53.14	$ 47.31	$ 31.55	$ 14.08	$ 13.49	$ 121.52	$ 39.68
	Sep'20	$ 257.58	$ 39.92	$ 33.88	$ 22.16	$ 8.79	$ 7.23	$ 132.83	$ 42.89
	Oct'20	$ 280.13	$ 30.04	$ 24.22	$ 15.56	$ 5.85	$ 4.41	$ 136.52	$ 45.67
	Nov'20	$ 295.55	$ 22.90	$ 17.48	$ 11.01	$ 4.03	$ 2.95	$ 136.14	$ 48.20
	Dec'20	$ 306.06	$ 17.84	$ 12.81	$ 7.90	$ 2.84	$ 2.08	$ 133.44	$ 50.53
	Jan'21	$ 313.12	$ 14.29	$ 9.58	$ 5.76	$ 2.05	$ 1.52	$ 129.37	$ 52.70
	Feb'21	$ 317.76	$ 11.80	$ 7.35	$ 4.29	$ 1.51	$ 1.15	$ 124.54	$ 54.74
	Mar'21	$ 320.68	$ 10.06	$ 5.80	$ 3.27	$ 1.14	$ 0.89	$ 119.30	$ 56.65
	Apr'21	$ 322.36	$ 8.84	$ 4.72	$ 2.57	$ 0.88	$ 0.71	$ 113.89	$ 58.45
	May'21	$ 323.12	$ 7.97	$ 3.96	$ 2.08	$ 0.70	$ 0.59	$ 108.47	$ 60.15
	Jun'21	$ 323.22	$ 7.36	$ 3.43	$ 1.73	$ 0.57	$ 0.50	$ 103.14	$ 61.75
	Jul'21	$ 322.82	$ 6.91	$ 3.05	$ 1.49	$ 0.49	$ 0.43	$ 97.96	$ 63.26
	Aug'21	$ 322.02	$ 6.58	$ 2.78	$ 1.32	$ 0.42	$ 0.38	$ 92.97	$ 64.69
	Sep'21	$ 320.93	$ 6.33	$ 2.58	$ 1.19	$ 0.38	$ 0.35	$ 88.19	$ 66.04
	Oct'21	$ 319.59	$ 6.14	$ 2.44	$ 1.10	$ 0.34	$ 0.32	$ 83.63	$ 67.31
	Nov'21	$ 318.05	$ 5.98	$ 2.33	$ 1.03	$ 0.32	$ 0.30	$ 79.29	$ 68.51
	Dec'21	$ 316.35	$ 5.86	$ 2.24	$ 0.98	$ 0.30	$ 0.28	$ 75.18	$ 69.65

Re: Apply roll rate multiplier to project future 24 month buckets on a roll forward basis

Reeza — Thu, 16 Jul 2020 17:47:41 GMT

What's the expected output if these are the inputs?
I doubt there's defined examples but it doesn't seem like a particularly hard calculation. Do you have SAS ETS licensed - PROC EXPAND or TIMESERIES may be useful here as well.

Re: Apply roll rate multiplier to project future 24 month buckets on a roll forward basis

koya — Thu, 16 Jul 2020 17:56:42 GMT

Thanks. I have SAS ETS. i will take a look. I use PROC EXPAND for rolling averages but never used to apply roll rates. output I am looking would look like this, only showing for 3 future months in the example. not able to paste the table here. updated original post. Thanks

Re: Apply roll rate multiplier to project future 24 month buckets on a roll forward basis

Reeza — Thu, 16 Jul 2020 18:03:27 GMT

Are those the expected values assuming this is your exact input? Or a sample of the format you'd want?

Re: Apply roll rate multiplier to project future 24 month buckets on a roll forward basis

koya — Thu, 16 Jul 2020 18:09:45 GMT

No, its just a sample. To get Jul'20 balances I have to multiply roll % from transition matrix. Getting July is simple but for August I need to used the calculated Jul'20 balances instead of using Jun'20 balances. Original transitional matrix should be applied on calculated month end balances roll forward basis

Re: Apply roll rate multiplier to project future 24 month buckets on a roll forward basis

Reeza — Thu, 16 Jul 2020 18:11:24 GMT

Please provide a worked example so we can verify the calculations.

Re: Apply roll rate multiplier to project future 24 month buckets on a roll forward basis

koya — Thu, 16 Jul 2020 19:09:34 GMT

updated the original post with worked out example in excel using MMULT function. Excel output is in matrix format.

Re: Apply roll rate multiplier to project future 24 month buckets on a roll forward basis

Reeza — Thu, 16 Jul 2020 20:04:11 GMT

Here's one way, inefficient in my mind but quick and dirty and seems right. I do not get the same answers you do though, so not sure why that's occurring. I'm going to assume your matrix wasn't as exactly specified or leave it to you to debug that portion.

*create fake data to work with;
data balance;
	infile cards dlm='09'x;
	input ME_BALANCE DLQ_STATUS;
	cards;
100	1
90	2
80	3
70	4
60	5
50	6
40	7
;
	;
	;;
run;

data transition_matrix;
	infile cards dlm='09'x;
	informat DLQ_STATUS DLQ_STATUS_NEXT_MONTH 8.roll_rate percent12.;
	format roll_rate percent12.2;
	input DLQ_STATUS DLQ_STATUS_NEXT_MONTH ROLL_RATE;
	cards;
1	1	97.99%
1	2	0.64%
1	3	0.00%
1	4	0.00%
1	5	0.00%
1	6	0.00%
1	7	0.00%
1	8	0.00%
1	9	0.00%
1	10	1.36%
2	1	30.45%
2	2	49.72%
2	3	18.16%
2	4	0.09%
2	5	0.04%
2	6	0.04%
2	7	0.10%
2	8	0.07%
2	9	0.08%
2	10	1.52%
3	1	13.57%
3	2	16.62%
3	3	37.65%
3	4	28.25%
3	5	0.15%
3	6	0.10%
3	7	0.29%
3	8	2.21%
3	9	0.16%
3	10	1.55%
4	1	16.21%
4	2	6.62%
4	3	12.54%
4	4	21.98%
4	5	16.74%
4	6	0.43%
4	7	0.45%
4	8	22.58%
4	9	0.93%
4	10	2.46%
5	1	18.64%
5	2	3.39%
5	3	3.23%
5	4	5.51%
5	5	16.59%
5	6	33.86%
5	7	0.58%
5	8	17.51%
5	9	2.54%
5	10	2.43%
6	1	29.97%
6	2	8.57%
6	3	13.83%
6	4	7.11%
6	5	7.54%
6	6	17.04%
6	7	16.64%
6	8	57.72%
6	9	5.35%
6	10	7.39%
7	1	2.62%
7	2	0.36%
7	3	0.11%
7	4	0.06%
7	5	0.05%
7	6	0.14%
7	7	0.14%
7	8	94.10%
7	9	1.43%
7	10	1.42%
;
	;
	;;
run;

*create data set for looping;
data balance1;
	set balance;
run;

*macro to apply rates;
%macro roll_rates(indsn=, outdsn=, t_matrix=, month=, year=);
    *Calculate rates for next month;
	proc sql;
		create table _calcs as select b.*, tm.DLQ_STATUS_NEXT_MONTH, 
			tm.roll_rate*b.me_balance as new_balance from &indsn. as b left 
			join &t_matrix. as tm on b.DLQ_STATUS=tm.DLQ_STATUS order by DLQ_STATUS, 
			DLQ_STATUS_NEXT_MONTH;
	quit;

    *get totals;
	proc means data=_calcs noprint nway;
		*where DLQ_STATUS_NEXT_MONTH=1;
		class DLQ_STATUS_NEXT_MONTH;
		var new_balance;
		output out=_summary sum=new_total;
	run;

    *rename and format data set;
	data &outdsn;
		set _summary;
		month_data=mdy(&month, 1, &year);
		format month_data yymm8.;
		rename DLQ_STATUS_next_month = DLQ_STATUS new_total = me_balance;
		drop _type_ _freq_;
	run;

   *delete intermediary calculations;
	proc datasets lib=work nodetails nolist;
		delete _cals _summary;
		run;
	quit;

%mend;

*test macro runs for one loop;
%roll_rates(indsn=balance1 , outdsn=balance2, t_matrix=transition_matrix , 
	month=1, year=2020);


*run macro for every month, this can be a data _null_ step instead if preferred but for ease of debugging;
*data _null_;
data roll_rates_execute;
	start_date='01Jun20'd;

	do i=1 to 24;*loop for number of months you want to roll;
		month_run=intnx('month', start_date, i-1, 's');
		year=year(month_run);
		month=month(month_run);
		str=catt('%roll_rates(indsn=balance', i, ' , outdsn=balance', i+1 , 
			' ,t_matrix=transition_matrix, month = ', month, ', year=', year, ');');
		output;
		call execute(str);
	end;
run;

*combine results together;
data _answer;
	set balance2-balance25;
run;

*transpose format if desired;
proc transpose data=_answer out= final_answer prefix = DLQ_STATUS;
	by month_data;
	id dlq_status;
	var me_balance;
run;

Re: Apply roll rate multiplier to project future 24 month buckets on a roll forward basis

koya — Thu, 16 Jul 2020 22:38:49 GMT

Thanks, this works!