topic How to code rating changes in SAS Programming

How to code rating changes

LanMin — Mon, 25 Mar 2013 17:15:07 GMT

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

Re: How to code rating changes

DBailey — Mon, 25 Mar 2013 17:26:55 GMT

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;

Re: How to code rating changes

LanMin — Tue, 26 Mar 2013 20:28:49 GMT

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

Re: How to code rating changes

DBailey — Wed, 27 Mar 2013 12:52:57 GMT

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.