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
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;
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?
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.