BookmarkSubscribeRSS Feed
LanMin
Fluorite | Level 6

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-

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

4 REPLIES 4
Haikuo
Onyx | Level 15

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
Fluorite | Level 6

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
Onyx | Level 15

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
Fluorite | Level 6

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

Lan

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 834 views
  • 0 likes
  • 2 in conversation