Hi I am trying to compute a 1 year migration matrix and cannot figure out how to do it...any help would be greatly appreciated!
My dataset looks like below (unbalanced data):
bond_id ratings start_year end_year
1 a 2000 2004
1 b 2005 2008
1 c 2009 2010
2 b 2003 2005
2 a 2006 2007
3 c 2001 2006
My thinking is to 1) Create multiple observations from one obs based on year so look like (I cannot figure out how to code this...):
bond_id ratings start_year end_year current_year_new nextyear_rating
1 a 2000 2004 2000 a
1 a 2000 2004 2001 a
1 a 2000 2004 2002 a
1 a 2000 2004 2003 a
1 a 2000 2004 2004 b
1 b 2005 2008 2005 b
1 b 2005 2008 2006 b
1 b 2005 2008 2007 b
1 b 2005 2008 2008 c
1 c 2009 2010 2009 c
1 c 2009 2010 2010 deflt
2 b 2003 2005 2003 b
2 b 2003 2005 2004 b
2 b 2003 2005 2005 a
2 a 2006 2007 2006 a
2 a 2006 2007 2007 deflt
3 c 2001 2006 2001 c
3 c 2001 2006 2002 c
3 c 2001 2006 2003 c
3 c 2001 2006 2004 c
3 c 2001 2006 2005 c
3 c 2001 2006 2006 deflt
2) then calculate the average of each
to
a b c deflt
from a 5/7 1/7 0/7 1/7
b 1/7 5/7 1/7 0/7
c 0/8 0/8 6/8 2/8
Deflt 0 0 0 1
Any help would be greatly appreciated!
Do all these bonds end up in default? There is no data about the default category. It looks like you assume an extra transition that is not in the data.
The idea to expand the data so that each row is one year is a good idea. You can do that with the DATA step. Just write a DO loop from start_year to end_year and use an OUTPUT stmt.
Once the data is in the expanded form, you can use the following IML code to build the Markov transition matrix:
data Bonds;
length rating next_rating $7;
input bond_id rating $ start_year end_year current_year next_rating $;
datalines;
1 a 2000 2004 2000 a
1 a 2000 2004 2001 a
1 a 2000 2004 2002 a
1 a 2000 2004 2003 a
1 a 2000 2004 2004 b
1 b 2005 2008 2005 b
1 b 2005 2008 2006 b
1 b 2005 2008 2007 b
1 b 2005 2008 2008 c
1 c 2009 2010 2009 c
1 c 2009 2010 2010 default
2 b 2003 2005 2003 b
2 b 2003 2005 2004 b
2 b 2003 2005 2005 a
2 a 2006 2007 2006 a
2 a 2006 2007 2007 default
3 c 2001 2006 2001 c
3 c 2001 2006 2002 c
3 c 2001 2006 2003 c
3 c 2001 2006 2004 c
3 c 2001 2006 2005 c
3 c 2001 2006 2006 default
;
proc iml;
use Bonds;
read all var {bond_id rating next_rating};
close;
R = unique( rating // next_rating );
numR = ncol(R);
C = j(numR, numR, 0);
mattrib C rowname=R colname=R;
do i = 1 to numR;
do j = 1 to numR;
C[i,j] = sum( rating=R[i] & next_rating=R[j] );
end;
end;
/* data does not contain any information about default transitions.
Assume default is end state */
C['default','default'] = 1;
C = C / C[,+]; /* divide each row by sum of row */
print C[format=fract20.];
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.
Find more tutorials on the SAS Users YouTube channel.