topic Computing 1 year migration matrix in SAS/IML Software and Matrix Computations

Computing 1 year migration matrix

Newph — Fri, 10 Mar 2017 04:18:26 GMT

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

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!

Re: Computing 1 year migration matrix

Rick_SAS — Fri, 10 Mar 2017 18:40:23 GMT

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.

Re: Computing 1 year migration matrix

Rick_SAS — Mon, 29 Oct 2018 20:04:32 GMT

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.];