How to code rating changes

[LanMin]

Hello,

Below is a small sample of a large data set. I would like to create a dummy variable that is based on changes in Rating –

Dto-plus (t-1)=1 ,  if a firm’s Rating changes from a middle notch in year t-1 to a plus notch in year t and maintains a plus notch (not necessarily the same rating as in year t) in year t+1 to t+2, and set to zero otherwise.

How should I program this?

Thanks!

p.s. rating notch was not a variable in the raw data, I put it there to make it easier for you to read, If I create this variable, is it better to use proc format with a numeric value?

firmid year rating rating notch

1001 1990 AAA middle

1001 1991 AA+ plus

1001 1992 AA+ plus

1001 1993 AA middle

1001 1994 AA+ plus

1002 1990 BBB+ plus

1002 1991 BBB+ plus

1002 1992 A- minus

1002 1993 A+ plus

1002 1994 A middle

1002 1995 A- minus

1003 1990 BBB middle

1003 1991 BBB- minus

1003 1992 BB+ plus

1003 1993 BB+ plus

1003 1994 BB- minus

1003 1995 BB- minus

Lan

[DBailey]

How about this..although I didn't see any case where the indicator variable was anything other than 0

DATA HAVE;

INPUT firmid year rating $ notch $;

CARDS;

1001 1990 AAA middle

1001 1991 AA+ plus

1001 1992 AA+ plus

1001 1993 AA middle

1001 1994 AA+ plus

1002 1990 BBB+ plus

1002 1991 BBB+ plus

1002 1992 A- minus

1002 1993 A+ plus

1002 1994 A middle

1002 1995 A- minus

1003 1990 BBB middle

1003 1991 BBB- minus

1003 1992 BB+ plus

1003 1993 BB+ plus

1003 1994 BB- minus

1003 1995 BB- minus

;

RUN;

PROC SQL;

CREATE TABLE WANT AS

SELECT

    Curr.*

    ,case when lastyear.notch='middle' and curr.notch='plus' and year1.notch='plus' and year2.notch='plus' then 1 else 0 end as Indicator_Var

FROM   

    HAVE as curr

    left join have as LastYear

        on curr.firmid=lastyear.firmid

            and curr.year-1=lastyear.year

    left join have as year1

        on curr.firmid=year1.firmid

            and curr.year+1=year1.year

    left join have as year2

        on curr.firmid=year2.firmid

            and curr.year+2=year2.year;

quit;

[LanMin]

Thanks so much!

Your way is so efficient. I thought I have to create lags. are there some books for advanced sas programming you would recommend?

-Lan

[DBailey]

sorry...my background is SQL, not SAS.  Even after using SAS for 10 years, I still find new SAS ways of doing things that are a surprise.