topic Re: Count number of downgrades in rating changes in SAS Programming

Count number of downgrades in rating changes

LanMin — Fri, 01 Mar 2013 22:24:58 GMT

Hi, experienced SAS users

Below is a sample of a large dataset that contains firm id,
year, and its debt ratings. Here is what I like to accomplish:

count instances that rating change by just one
level (e.g. A+ to A, B+ to B), and output it to a separate data set.
After 1 is done, is it possible to modify the
code to instances that rating change by more than one level (e.g. A+ to AA-), and
output it to a separate data set.

Thanks in advance,

Lan

+++++++++++++++++++++++++

data sample;

input ID year rating$;

datalines;

1004 1990 A+

1004 1991 A

1004 1992 AA-

1004 1993 AA-

1004 1994 AA

1006 1991 B

1006 1992 B+

1006 1993 B

1006 1993 B-

+++++++++++++++++++++++++

Re: Count number of downgrades in rating changes

Haikuo — Sat, 02 Mar 2013 05:39:36 GMT

Since your question is lacking details (for example, your output sample data is desired here), I am not sure I have completely understand it, so my approach is at the risk of either being off or being preliminary.

SAS needs to know your "level ranking system", you could use many methods to achieve that, it is basically called "look up" technique. The following code adopts "format" approach. You didn't say when your condition is met, which record to output, the current one or the pair, I choose to output them both, even though as you can see in the results, certain obs are possible to output into two different tables at the same time.

proc format;

invalue rating

'B-'=1

'B'=2

'B+'=3

'BB-'=4

'BB'=5

'BB+'=6

'A-'=7

'A'=8

'A+'=9

'AA-'=10

'AA'=11

'AA+'=12

;

RUN;

data sample;

input ID year rating$;

_rating=input(rating,rating.);

datalines;

1004 1990 A+

1004 1991 A

1004 1992 AA-

1004 1993 AA-

1004 1994 AA

1006 1991 B

1006 1992 B+

1006 1993 B

1006 1993 B-

;

data one two_up;

set sample;

by id notsorted;

set sample (firstobs=2 keep=_rating rename=_rating=_lead) sample (obs=1 drop=_all_);

_lag=ifn(first.id,.,lag(_rating));

_lead=ifn(last.id,.,_lead);

if abs(_lag-_rating)=1 or abs(_lead-_rating)=1 then output one;

if abs(_lag-_rating)>1 or abs(_lead-_rating)>1 then output two_up;

drop _lead _lag;

run;

Haikuo

Re: Count number of downgrades in rating changes

LanMin — Sun, 03 Mar 2013 02:14:37 GMT

Hai.Kuo,

Thanks for the help !

You are right, I should be more clear about expected output.

Using data below

data
sample;

input ID year rating$;

_rating=input(rating,rating.);

datalines;

1004 1990 A+

1004 1991 A

1004 1992 AA+

1004 1993 AA-

1004 1994 AA

1004 1995 AA-

1006 1991 B

1006 1992 B+

1006 1993 B

1006 1994 B

1006 1995 B-

;

How to change your code to output

Rating change by one notch for the adjacent
two years: i.e. expected output dataset should look like this:

1004 1991 A

1004 1995 AA-

1006 1995 B-

These three records corresponds to years that rating value decreased by 1 from prior year.

Rating change by two notches for the adjacent two years: i.e. expected output dataset should look like this:

1004 1993 AA-

This is the year rating dropped from 1992 AA+ by 2 notches to AA-.

I know your code works, but could you add some
comments to explain it. I am still new to sas coding. If you have some
reference for look up technique, please also share.

Thanks again,

Lan

Re: Count number of downgrades in rating changes

Haikuo — Sun, 03 Mar 2013 02:27:38 GMT

That would make the problem a LOT simpler. Please see below simplified code:

proc format;

invalue rating

'B-'=1

'B'=2

'B+'=3

'BB-'=4

'BB'=5

'BB+'=6

'A-'=7

'A'=8

'A+'=9

'AA-'=10

'AA'=11

'AA+'=12

;

RUN;

data

sample;

input ID year rating$;

_rating=input(rating,rating.);

datalines;

1004 1990 A+

1004 1991 A

1004 1992 AA+

1004 1993 AA-

1004 1994 AA

1004 1995 AA-

1006 1991 B

1006 1992 B+

1006 1993 B

1006 1994 B

1006 1995 B-

;

data one two_up;

set sample;

by id year;

_lag=ifn(first.id,.,lag(_rating));/this is to create a variable that has the _rating value of previous obs*/

if _lag-_rating=1 then output one; /*if down one notch, then output to 'one' */

else if _lag-_rating>1 then output two_up; /*if down two or more notch, then output to 'two'*/

drop _:;

run;

Haikuo

Update with comments and some links related to SAS 'look up';

http://www2.sas.com/proceedings/sugi27/p011-27.pdf

http://www.ssc.wisc.edu/sscc/pubs/4-1.pdf

http://www.nesug.org/Proceedings/nesug10/cc/cc37.pdf

pretty much everything in term of 'look up' technique.

Re: Count number of downgrades in rating changes

LanMin — Mon, 04 Mar 2013 03:08:49 GMT

Thanks so much !!! Your help is much appreciated.

Lan