Count number of downgrades in rating changes

[LanMin]

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:

1. 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.
2. 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-

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

[Haikuo]

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

[LanMin]

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

1. 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.

1.    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

[Haikuo]

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.

[LanMin]

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

Lan