topic Re: Transition matrix smoothing in SAS Programming

Transition matrix smoothing

ReadyPlayer1 — Wed, 29 Nov 2023 05:24:00 GMT

I have a dataset with 4 variables: ID, date, rating_start, rating_end. The transition matrix from rating_start to rating_end is shown below for a 1-year period (not real data). I want to smooth each row so that it is monotonic (different direction either side of the diagonal). I have highlighted the cells where the data is not monotonically decreasing or increasing. I have found several published papers where the average of the non-monotonic cells is used but I cannot work out how to do this in SAS. I have hundreds of matrices that I need to do this for, which need to be regularly updated.

Before smoothing:

Desired after smoothing:

Re: Transition matrix smoothing

sbxkoenk — Wed, 29 Nov 2023 08:34:34 GMT

That's easy to do with data step programming (array processing).
But you can also use IML for it (IML = the SAS Interactive Matrix Language).

Can you provide us with one such matrix? A datastep with datalines / cards, I mean ... not a picture.

Thanks,

Koen

Re: Transition matrix smoothing

Ksharp — Wed, 29 Nov 2023 11:44:06 GMT

Calling @Rick_SAS

Re: Transition matrix smoothing

Rick_SAS — Wed, 29 Nov 2023 12:04:19 GMT

> I have found several published papers where the average of the non-monotonic cells is used but I cannot work out how to do this in SAS

Please post the citation to the paper whose algorithm you want to use.
Also tell us whether you can use SAS IML language or if you are restricted to the DATA step.
In what sense are these "transition matrices"? In probability and statistics, a transition matrix is a square matrix in which the (i,j)th element is the probability of transitioning from state i into state j. The sum of each row is 1.

In general, this is not a simple operation and might require an iterative algorithm. The example you provided only has adjacent pairs that are not monotone. But you can construct examples where doing a pairwise average will not result in a monotonically decreasing row. For example, if the first row is

{4000, 2000, 600, 700, 700, 500}

and you attempt to perform "pairwise averaging" whenever adjacent cells are not decreasing, then after one pass the vector is not monotone. The example is monotone after a second pass, but you can construct other examples that need additional iterations.

proc iml;
M = {
4000 2000 600 700 700 500
};
/* first pass */
do i = 1 to ncol(M)-1;
   if M[i+1] > M[i] then 
      M[i:i+1] = (M[i+1] + M[i]) / 2;
end;
print M;
/* second pass */
do i = 1 to ncol(M)-1;
   if M[i+1] > M[i] then 
      M[i:i+1] = (M[i+1] + M[i]) / 2;
end;
print M;

Re: Transition matrix smoothing

WarrenKuhfeld — Wed, 29 Nov 2023 12:20:38 GMT

Check out the OPSCAL function in IML. It can average adjacent categories to achieve monotonicity. https://documentation.sas.com/doc/en/pgmsascdc/v_045/casimllang/casimllang_common_sect279.htm

Re: Transition matrix smoothing

sbxkoenk — Wed, 29 Nov 2023 14:28:08 GMT

Hello @ReadyPlayer1 ,

I replied quickly this morning ... a few minutes before my meeting started. I maintain that the matrix you posted is extremely easy to smooth (in the sense you meant and indicated). However, I think it is a very simple example and so I agree with @Rick_SAS that it can quickly become much more difficult and much less obvious. I hope you get out with the answers provided by @Rick_SAS and @WarrenKuhfeld
and otherwise just ask for additional help.

Good luck,

Koen

Re: Transition matrix smoothing

ReadyPlayer1 — Wed, 29 Nov 2023 23:11:14 GMT

Thanks everyone for your input, this is my first time using this community, really impressed with how quick you all are!

1) Smoothing algorithms by constrained maximum likelihood: methodologies and implementations for Comprehensive Capital Analysis and Review stress testing and International Financial Reporting Standard 9 expected credit loss estimation - Bill Huajian Yang (https://www.risk.net/journal-of-risk-model-validation/5587081/smoothing-algorithms-by-constrained-maximum-likelihood-methodologies-and-implementations-for-comprehensive-capital-analysis-and-review-stress-testing-and-international-financial-reporting-standard-9-expected). There is a sentence "These algorithms can be implemented by a modeler using, for example, the SAS PROC NLMIXED package."

2) I do not have IML available, only datastep

3) These are the counts of transitions; I will produce the % transitions using the processed count matrices.

Re: Transition matrix smoothing

Rick_SAS — Wed, 29 Nov 2023 23:26:01 GMT

> I do not have IML available, only datastep

Thanks for the response. I will leave you in the capable hands of the other experts. Best wishes.

Re: Transition matrix smoothing

WarrenKuhfeld — Wed, 29 Nov 2023 23:58:24 GMT

Opscal functionality is also available from the SAS/STAT procedure transreg.

Re: Transition matrix smoothing

Ksharp — Fri, 01 Dec 2023 09:12:43 GMT

You could fit a growth curve as mentioned in

Fit a growth curve in SAS - The DO Loop

188-29: Repeated Measures Modeling with PROC MIXED (sas.com)