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
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;
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.