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.
Click image to register for webinar
