Estimate transition probability matrix

[Ruslan]

Dear All,

Could you please help me to estimate the following probability transition matrix?

I have companies each month with certain values like this:

Month      Company      Value

1                    1                10

1                    2                14

1                    5                15.5

1                    7                8

1                    9                3

1                   10               11

1                   13               16

1                   15               12

1                   16               19

1                   18               7

1                   20               11

1                   21               5

1                   22               8

2                   1                 9

2                   2                13

2                   3                20

2                   4                2

2                   5                19

2                   8                9

2                  10               10

2                  11               7

2                  12               13

2                  13               3

2                  15               20

2                  16               23

2                  18               17

2                  20               21

2                  21               8

I need to do the following. First, sort companies in ascending order into ten groups (portfolios) each month (in total I have 203 months) based on their value. Second, I need to obtain the average of the month-to-month transition matrices for the companies in these groups i.e. the average probability (in percent) that a company in group i (presumably, given by the rows of the matrix) in one month will be in group j (possibly given by the columns of the matrix) in the subsequent month.

So the martrix should be something like this:

              1                                                                                                         2         3         4         5         6         7          8         9        10

1   prob(firm in group 1 in month t remains to be in group 1 in month t+1)

2   prob(firm in group 2 in month t moves to group 1 in month t+1)

3      ...

4     ...

5     ...

6     ... 

7     ...

8     ...

9     ...

10 prob(firm in group 10 in month t moves to group 1 in month t+1)

I would hugely appreciate any help since I have spent entire day attempting to write a code, but got nothing at the end of the day. Please, please help me with this matrix.

Kind regards,

Ruslan

[Rick_SAS]

What tools are you using?  Do you have SAS/ETS software (PROC ENTROPY) or SAS/IML software?  Or are you trying to compute this in Base SAS?

[Ruslan]

Hi Rick.

It does not really matter. I have both ETS and IML, so any suggestions on how to construct this matrix in any of those softwares will be hugely appreciated.

Kind regards,

Ruslan

[PGStats · Accepted solution]

I had to simulate data to get a meaningful example...

data have;
call streaminit(85765);
do month = 1 to 10;
    do Company =   1, 2, 5, 7, 9, 10, 13, 15, 16, 18, 20, 21, 22;
        value = round(rand("UNIFORM") * 25, 0.5);
        output;
        end;
    end;
run;

proc rank data=have out=haveRanks groups=10;
by month;
var value;
ranks group;
run;

proc sql;
create table trans as
select a.group as from, b.group as to, count(*) as nTrans
from haveRanks as a inner join haveRanks as b
    on a.company=b.company and a.month+1 = b.month
group by a.group, b.group;
create table probs as 
select from, to, nTrans/sum(nTrans) as prob
from trans
group by from;
quit;

proc transpose data=probs out=transTable(drop = _name_) prefix=to_;
by from;
id to;
var prob;
run;

/* reorder variables, assign format to probabilities (optional) */
data transTable;
retain from to_0-to_9;
format to_0-to_9 pvalue5.3;
set transTable;
run;

proc print data=transTable noobs; run;