topic Re: Roll Rate Model in SAS Programming

Roll Rate Model

greg6363 — Mon, 10 May 2021 18:09:48 GMT

OK, I am half way through constructing a roll rate model that will create a matrix for the week over week transitions for accounts between different delinquency tranches. I have created the SAS dataset (dq_transitions_2) with the account level details which is attached below. Now I am working on the macro to process the transitions that will produce the matrix but I am running into problems with the error messages and the matrix is not generated in the output. Here is the code for the macro:

%let dt1 = '24APR2021'd;/*Current*/
%let dt2 = '17APR2021'd;/*Previous*/

%macro RR(Start_Date,end_Date);

data Input_Pbal_Loan_level;
set dq_transitions_2;
if date_1 =&Start_Date. and date_2=&End_Date.;
keep delq_bucket: Principal_: date:;
run;

/*Get the start date ,principal balance*/
proc sql;
create table Input_Pbal_total as
select a.delq_bucket_1,a.Date_1 format mmddyy8. label='Date_1',
sum(principal_1) as Outgoing_Principal format dollar14.2 label='Outgoing_Principal'
from Input_Pbal_Loan_level a
group by Date_1,Delq_bucket_1;
quit;

/*Get the principal balance for end_Date*/
proc sql;
create table Output_Pbal_total_0 as
select a.delq_bucket_1,a.Date_2 format mmddyy8. label='Date_2',
a.delq_bucket_2,sum(principal_2) as Output_Principal format dollar14.2 label='Output_Principal'
from Input_Pbal_Loan_level a
group by delq_bucket_1,a.date_2,Delq_bucket_2;
quit;

proc transpose data=Output_Pbal_total_0 out=Output_Pbal_total_T(drop=_name_) ;
by delq_bucket_1 Date_2 ;
id Delq_bucket_2;
var Output_Principal;
run;

proc sql;
create table RR_Matrix_0 as
select a.delq_bucket_1, a.Date_1 format mmddyy8. label='Date_1',
b.date_2 format mmddyy8. label='Date_2',a.Outgoing_Principal format dollar14.2 label='Outgoing_Principal',
b.*
from Input_Pbal_total a
left join Output_Pbal_total_T b
on a.Delq_bucket_1=b.Delq_bucket_1;
quit;

data RR_Matrix;
retain delq_bucket_1 date_1 date_2 Outgoing_principal "A. Outgoing" ;
set RR_Matrix_0;
run;
proc datasets lib=work nolist ;delete RR_Matrix_0 ;run;

%mend;
%rr('17APR2021'd,'24APR2021'd);

I have enclosed an illustration (in an attached file - RR_Matrix) on how the matrix output should look when produced by the code. Any assistance would be greatly appreciated. Thanks.

Re: Roll Rate Model

novinosrin — Sun, 02 May 2021 23:42:00 GMT

Hi @greg6363 I am very keen on the roll rate models as I work with the same for our Home equity/Residential mortgage lending portfolios. I haven't really looked through your code in detail nor your document/data yet as I have been swamped at work and consequently haven't been able to contribute here much lately.

Quick question: Is it very urgent or can it wait until mid week until 5th May? If yes, I would love to provide you with a robust solution and you could use/reuse and abuse lol.

We at C'bank mostly deal with monthly performance monitoring of delinquencies etc. What products are you guys dealing with? Can you offer some more details? Also can you make a small sample of your input data besides what you have already shared along with its expected out and business logic,

Is it Roll To vs Rolling? and other details plz.

Thank you & Regards,

Re: Roll Rate Model

greg6363 — Mon, 03 May 2021 03:34:50 GMT

novinosrin, thanks for responding to my post. Yes, I can wait until later this week but let's continue the conversation offline since a lot of the terms will be beyond the scope of this discussion board You can send a message to my Yahoo account with my ID which is referenced at the top of your post. Please confirm. Thanks.

Re: Roll Rate Model

novinosrin — Mon, 03 May 2021 19:45:54 GMT

Hey @greg6363 Much thanks for your response too. It's certainly a win-win prospect as I get an opportunity to expand my knowledge in return :). Please be aware, I am in Stamford-Bridgeport area Connecticut and will respond to you in ET(Eastern time zone).

Sure, will message you offline if you prefer that. Just bear with me early on this week as I am working on various "Forbearance and other TDR(Trouble debt restructuring) modification reports" with some being internal and some of course for regulatory body like OCC.

I am sure once we get started, I would seek some leads from you in understanding the scope thoroughly as I am only familiar with risk measures of secure portfolios, however the structure seems very similar.

So overall, we should get started very soon. I am on!

Re: Roll Rate Model

greg6363 — Mon, 03 May 2021 22:44:13 GMT

Thanks, novinosrin. Go ahead and send a email message to the Yahoo account with my ID so that we can set up our line of communication for this discussion. Thanks.

Re: Roll Rate Model

novinosrin — Sat, 08 May 2021 20:45:52 GMT

HI @greg6363 It appears you are perhaps looking for something as simple as-


/*Create the deck aka base set*/
proc sql;
 create table temp as
 select date_1,Delq_Bucket_1,date_2,Delq_Bucket_2,
        count_start,
        amount_start,
		count(*) as count_end,
		sum(principal_2) as amount_end format=dollar20.2
 from
 (select *,
        count(acct_number) as count_start,
		sum(principal_1) as amount_start format=dollar20.2
 from rr.dq_transitions_2 /*Your dq_transitions_2 dataset*/
 group by date_1,Delq_Bucket_1)
 group by date_1,Delq_Bucket_1,date_2,Delq_Bucket_2,
        count_start,
        amount_Start;
quit;

proc transpose data=temp out=temp1;
 by date_1 Delq_Bucket_1 date_2 Delq_Bucket_2;
 var count_end amount_end;
run;
proc transpose data=temp1 out=want_units_dollars(drop=_name_) delimiter=_;
 by date_1 Delq_Bucket_1 date_2;
 var col1;
 id _name_ Delq_Bucket_2 ;
 format amount_end: dollar20.2;
run;

RESULT:

date_1	Delq_Bucket_1	date_2	count_end_B. Current	amount_end_B. Current	count_end_C. 1-7	amount_end_C. 1-7	count_end_D. 8-14	amount_end_D. 8-14	count_end_F. 22-28	amount_end_F. 22-28	count_end_H. 36-42	amount_end_H. 36-42	count_end_I. 43-49	amount_end_I. 43-49	count_end_K. 57-65	amount_end_K. 57-65	count_end_A.Outgoing	amount_end_A.Outgoing	count_end_E. 15-21	amount_end_E. 15-21	count_end_G. 29-35	amount_end_G. 29-35	count_end_J. 50-56	amount_end_J. 50-56	count_end_L. 65+	amount_end_L. 65+
4/17/2021	A.Incoming	4/24/2021	2415	$2,565,206.39	3	$2,583.41	2	$1,274.13	4	$1,140.40	1	$67.72	1	$513.20	1	$54.07	.	.	.	.	.	.	.	.	.	.
4/17/2021	B. Current	4/24/2021	23045	$21,372,372.96	312	$316,571.78	295	$311,955.24	6	$5,135.32	.	.	.	.	.	.	1232	.	4	$3,147.84	.	.	.	.	.	.
4/17/2021	C. 1-7	4/24/2021	159	$167,274.60	251	$260,219.96	571	$606,809.55	189	$214,580.65	3	$3,674.90	.	.	.	.	63	.	1	$163.97	.	.	.	.	.	.
4/17/2021	D. 8-14	4/24/2021	25	$26,640.48	466	$513,203.78	129	$139,940.23	.	.	.	.	.	.	1	$1,188.79	47	.	164	$171,312.45	2	$2,704.11	.	.	.	.
4/17/2021	E. 15-21	4/24/2021	4	$1,980.64	1	$2,000.00	16	$13,798.47	404	$403,314.51	155	$177,774.29	.	.	1	$2,400.00	39	.	108	$101,467.98	.	.	1	$923.24	.	.
4/17/2021	F. 22-28	4/24/2021	4	$5,598.05	8	$9,953.51	11	$14,125.13	82	$78,033.94	1	$609.22	3	$2,674.19	.	.	27	.	294	$300,588.43	113	$118,754.54	.	.	.	.
4/17/2021	G. 29-35	4/24/2021	3	$1,845.28	3	$1,342.57	1	$483.90	5	$3,957.16	246	$243,576.54	.	.	.	.	92	.	1	$557.23	56	$49,454.15	76	$72,242.50	.	.
4/17/2021	H. 36-42	4/24/2021	.	.	.	.	.	.	.	.	49	$47,592.40	67	$62,678.51	.	.	34	.	2	$1,509.75	220	$221,980.80	.	.	.	.
4/17/2021	I. 43-49	4/24/2021	.	.	.	.	.	.	.	.	6	$5,228.20	39	$38,110.97	49	$47,360.83	35	.	.	.	1	$1,700.00	128	$120,992.60	.	.
4/17/2021	J. 50-56	4/24/2021	2	$151.68	.	.	.	.	.	.	.	.	129	$127,462.04	37	$35,922.15	17	.	.	.	3	$2,659.38	20	$24,610.03	.	.
4/17/2021	K. 57-65	4/24/2021	.	.	.	.	.	.	.	.	.	.	.	.	34	$30,117.22	65	.	.	.	.	.	1	$1,142.08	.	.
4/17/2021	L. 65+	4/24/2021	.	.	.	.	.	.	.	.	.	.	.	.	.	.	1	.	.	.	.	.	.	.	5	$5,824.92

Re: Roll Rate Model

novinosrin — Mon, 10 May 2021 12:19:02 GMT

Hi @greg6363 For Roll rates, all we need to is use the base set or deck that we created in the 1st step and just modify with-

calculated count_end/count_start as Roll_rates_units format=percent20.2,
calculated amount_end/amount_start as Roll_rates_dollar format=percent20.2

Here is the modified full version of the above code with roll rates-


/*Create the deck aka base set*/
proc sql;
 create table temp as
 select date_1,Delq_Bucket_1,date_2,Delq_Bucket_2,
        count_start,
        amount_start,
		count(*) as count_end,
		sum(principal_2) as amount_end format=dollar20.2,
		calculated count_end/count_start as Roll_rates_units format=percent20.2,
        calculated amount_end/amount_start as Roll_rates_dollar format=percent20.2
 from
 (select *,
        count(acct_number) as count_start,
		sum(principal_1) as amount_start format=dollar20.2
 from rr.dq_transitions_2 /*Your dq_transitions_2 dataset*/
 group by date_1,Delq_Bucket_1)
 group by date_1,Delq_Bucket_1,date_2,Delq_Bucket_2,
        count_start,
        amount_Start;
quit;

proc transpose data=temp out=temp1;
 by date_1 Delq_Bucket_1 date_2 Delq_Bucket_2;
 var count_end amount_end  Roll_rates_:;
run;
proc transpose data=temp1 out=want_units_dollars(drop=_name_) delimiter=_;
 by date_1 Delq_Bucket_1 date_2;
 var col1;
 id _name_ Delq_Bucket_2 ;
 format amount_end: dollar20.2 roll_rates: percent10.2;
run;

RESULT:

date_1	Delq_Bucket_1	date_2	count_end_B. Current	amount_end_B. Current	Roll_rates_units_B. Current	Roll_rates_dollar_B. Current	count_end_C. 1-7	amount_end_C. 1-7	Roll_rates_units_C. 1-7	Roll_rates_dollar_C. 1-7	count_end_D. 8-14	amount_end_D. 8-14	Roll_rates_units_D. 8-14	Roll_rates_dollar_D. 8-14	count_end_F. 22-28	amount_end_F. 22-28	Roll_rates_units_F. 22-28	Roll_rates_dollar_F. 22-28	count_end_H. 36-42	amount_end_H. 36-42	Roll_rates_units_H. 36-42	Roll_rates_dollar_H. 36-42	count_end_I. 43-49	amount_end_I. 43-49	Roll_rates_units_I. 43-49	Roll_rates_dollar_I. 43-49	count_end_K. 57-65	amount_end_K. 57-65	Roll_rates_units_K. 57-65	Roll_rates_dollar_K. 57-65	count_end_A.Outgoing	amount_end_A.Outgoing	Roll_rates_units_A.Outgoing	Roll_rates_dollar_A.Outgoing	count_end_E. 15-21	amount_end_E. 15-21	Roll_rates_units_E. 15-21	Roll_rates_dollar_E. 15-21	count_end_G. 29-35	amount_end_G. 29-35	Roll_rates_units_G. 29-35	Roll_rates_dollar_G. 29-35	count_end_J. 50-56	amount_end_J. 50-56	Roll_rates_units_J. 50-56	Roll_rates_dollar_J. 50-56	count_end_L. 65+	amount_end_L. 65+	Roll_rates_units_L. 65+	Roll_rates_dollar_L. 65+
4/17/2021	A.Incoming	4/24/2021	2415	$2,565,206.39	99.51%	.	3	$2,583.41	0.12%	.	2	$1,274.13	0.08%	.	4	$1,140.40	0.16%	.	1	$67.72	0.04%	.	1	$513.20	0.04%	.	1	$54.07	0.04%	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.
4/17/2021	B. Current	4/24/2021	23045	$21,372,372.96	92.57%	91.55%	312	$316,571.78	1.25%	1.36%	295	$311,955.24	1.19%	1.34%	6	$5,135.32	0.02%	0.02%	.	.	.	.	.	.	.	.	.	.	.	.	1232	.	4.95%	.	4	$3,147.84	0.02%	0.01%	.	.	.	.	.	.	.	.	.	.	.	.
4/17/2021	C. 1-7	4/24/2021	159	$167,274.60	12.85%	12.65%	251	$260,219.96	20.29%	19.68%	571	$606,809.55	46.16%	45.89%	189	$214,580.65	15.28%	16.23%	3	$3,674.90	0.24%	0.28%	.	.	.	.	.	.	.	.	63	.	5.09%	.	1	$163.97	0.08%	0.01%	.	.	.	.	.	.	.	.	.	.	.	.
4/17/2021	D. 8-14	4/24/2021	25	$26,640.48	3.00%	2.96%	466	$513,203.78	55.88%	57.07%	129	$139,940.23	15.47%	15.56%	.	.	.	.	.	.	.	.	.	.	.	.	1	$1,188.79	0.12%	0.13%	47	.	5.64%	.	164	$171,312.45	19.66%	19.05%	2	$2,704.11	0.24%	0.30%	.	.	.	.	.	.	.	.
4/17/2021	E. 15-21	4/24/2021	4	$1,980.64	0.55%	0.26%	1	$2,000.00	0.14%	0.27%	16	$13,798.47	2.19%	1.83%	404	$403,314.51	55.42%	53.48%	155	$177,774.29	21.26%	23.57%	.	.	.	.	1	$2,400.00	0.14%	0.32%	39	.	5.35%	.	108	$101,467.98	14.81%	13.45%	.	.	.	.	1	$923.24	0.14%	0.12%	.	.	.	.
4/17/2021	F. 22-28	4/24/2021	4	$5,598.05	0.74%	0.99%	8	$9,953.51	1.47%	1.77%	11	$14,125.13	2.03%	2.51%	82	$78,033.94	15.10%	13.84%	1	$609.22	0.18%	0.11%	3	$2,674.19	0.55%	0.47%	.	.	.	.	27	.	4.97%	.	294	$300,588.43	54.14%	53.31%	113	$118,754.54	20.81%	21.06%	.	.	.	.	.	.	.	.
4/17/2021	G. 29-35	4/24/2021	3	$1,845.28	0.62%	0.39%	3	$1,342.57	0.62%	0.28%	1	$483.90	0.21%	0.10%	5	$3,957.16	1.04%	0.83%	246	$243,576.54	50.93%	51.28%	.	.	.	.	.	.	.	.	92	.	19.05%	.	1	$557.23	0.21%	0.12%	56	$49,454.15	11.59%	10.41%	76	$72,242.50	15.73%	15.21%	.	.	.	.
4/17/2021	H. 36-42	4/24/2021	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	49	$47,592.40	13.17%	12.96%	67	$62,678.51	18.01%	17.07%	.	.	.	.	34	.	9.14%	.	2	$1,509.75	0.54%	0.41%	220	$221,980.80	59.14%	60.45%	.	.	.	.	.	.	.	.
4/17/2021	I. 43-49	4/24/2021	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	6	$5,228.20	2.33%	2.13%	39	$38,110.97	15.12%	15.52%	49	$47,360.83	18.99%	19.29%	35	.	13.57%	.	.	.	.	.	1	$1,700.00	0.39%	0.69%	128	$120,992.60	49.61%	49.27%	.	.	.	.
4/17/2021	J. 50-56	4/24/2021	2	$151.68	0.96%	0.07%	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	129	$127,462.04	62.02%	60.84%	37	$35,922.15	17.79%	17.15%	17	.	8.17%	.	.	.	.	.	3	$2,659.38	1.44%	1.27%	20	$24,610.03	9.62%	11.75%	.	.	.	.
4/17/2021	K. 57-65	4/24/2021	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	34	$30,117.22	34.00%	31.10%	65	.	65.00%	.	.	.	.	.	.	.	.	.	1	$1,142.08	1.00%	1.18%	.	.	.	.
4/17/2021	L. 65+	4/24/2021	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	1	.	16.67%	.	.	.	.	.	.	.	.	.	.	.	.	.	5	$5,824.92	83.33%	86.61%

Re: Roll Rate Model

novinosrin — Mon, 10 May 2021 12:18:15 GMT

@greg6363 In my humble opinion and for reading ease, I'd rather keep the roll rates matrix separate and not squeeze in like what i posted in the previous. Therefore, Rollrates piecemeal code would be-


/*Create the deck aka base set*/
proc sql;
 create table temp as
 select date_1,Delq_Bucket_1,date_2,Delq_Bucket_2,
        count_start,
        amount_start,
		count(*) as count_end,
		sum(principal_2) as amount_end format=dollar20.2,
		calculated count_end/count_start as Roll_rates_units format=percent20.2,
        calculated amount_end/amount_start as Roll_rates_dollar format=percent20.2
 from
 (select *,
        count(acct_number) as count_start,
		sum(principal_1) as amount_start format=dollar20.2
 from rr.dq_transitions_2 /*Your dq_transitions_2 dataset*/
 group by date_1,Delq_Bucket_1)
 group by date_1,Delq_Bucket_1,date_2,Delq_Bucket_2,
        count_start,
        amount_Start;
quit;

proc transpose data=temp out=temp1;
 by date_1 Delq_Bucket_1 date_2 Delq_Bucket_2;
 var  Roll_rates_:;
run;
proc transpose data=temp1 out=roll_rates(drop=_name_) delimiter=_;
 by date_1 Delq_Bucket_1 date_2;
 var col1;
 id _name_ Delq_Bucket_2 ;
 format roll_rates: percent10.2;
run;

RESULT:

date_1	Delq_Bucket_1	date_2	Roll_rates_units_B. Current	Roll_rates_dollar_B. Current	Roll_rates_units_C. 1-7	Roll_rates_dollar_C. 1-7	Roll_rates_units_D. 8-14	Roll_rates_dollar_D. 8-14	Roll_rates_units_F. 22-28	Roll_rates_dollar_F. 22-28	Roll_rates_units_H. 36-42	Roll_rates_dollar_H. 36-42	Roll_rates_units_I. 43-49	Roll_rates_dollar_I. 43-49	Roll_rates_units_K. 57-65	Roll_rates_dollar_K. 57-65	Roll_rates_units_A.Outgoing	Roll_rates_dollar_A.Outgoing	Roll_rates_units_E. 15-21	Roll_rates_dollar_E. 15-21	Roll_rates_units_G. 29-35	Roll_rates_dollar_G. 29-35	Roll_rates_units_J. 50-56	Roll_rates_dollar_J. 50-56	Roll_rates_units_L. 65+	Roll_rates_dollar_L. 65+
04/17/2021	A.Incoming	04/24/2021	99.51%		0.12%		0.08%		0.16%		0.04%		0.04%		0.04%
04/17/2021	B. Current	04/24/2021	92.57%	91.55%	1.25%	1.36%	1.19%	1.34%	0.02%	0.02%							4.95%		0.02%	0.01%
04/17/2021	C. 1-7	04/24/2021	12.85%	12.65%	20.29%	19.68%	46.16%	45.89%	15.28%	16.23%	0.24%	0.28%					5.09%		0.08%	0.01%
04/17/2021	D. 8-14	04/24/2021	3.00%	2.96%	55.88%	57.07%	15.47%	15.56%							0.12%	0.13%	5.64%		19.66%	19.05%	0.24%	0.30%
04/17/2021	E. 15-21	04/24/2021	0.55%	0.26%	0.14%	0.27%	2.19%	1.83%	55.42%	53.48%	21.26%	23.57%			0.14%	0.32%	5.35%		14.81%	13.45%			0.14%	0.12%
04/17/2021	F. 22-28	04/24/2021	0.74%	0.99%	1.47%	1.77%	2.03%	2.51%	15.10%	13.84%	0.18%	0.11%	0.55%	0.47%			4.97%		54.14%	53.31%	20.81%	21.06%
04/17/2021	G. 29-35	04/24/2021	0.62%	0.39%	0.62%	0.28%	0.21%	0.10%	1.04%	0.83%	50.93%	51.28%					19.05%		0.21%	0.12%	11.59%	10.41%	15.73%	15.21%
04/17/2021	H. 36-42	04/24/2021									13.17%	12.96%	18.01%	17.07%			9.14%		0.54%	0.41%	59.14%	60.45%
04/17/2021	I. 43-49	04/24/2021									2.33%	2.13%	15.12%	15.52%	18.99%	19.29%	13.57%				0.39%	0.69%	49.61%	49.27%
04/17/2021	J. 50-56	04/24/2021	0.96%	0.07%									62.02%	60.84%	17.79%	17.15%	8.17%				1.44%	1.27%	9.62%	11.75%
04/17/2021	K. 57-65	04/24/2021													34.00%	31.10%	65.00%						1.00%	1.18%
04/17/2021	L. 65+	04/24/2021															16.67%								83.33%	86.61%