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