gvkey | year | RET |
1004 | 1992 | -0.04762 |
1004 | 1993 | 0.0138 |
1004 | 1994 | -0.00429 |
1004 | 1995 | -0.14493 |
1004 | 1996 | 0.067797 |
1004 | 1997 | 0.107302 |
1004 | 1998 | 0.072464 |
1004 | 1999 | 0.057027 |
1331 | 1992 | 0.038333 |
1331 | 1993 | -0.05405 |
1331 | 1994 | -0.04286 |
1331 | 1995 | -0.03343 |
1331 | 1996 | -0.03125 |
24456 | 1992 | -0.02621 |
24456 | 1993 | 0.006377 |
24456 | 1994 | 0 |
24456 | 1995 | 0.05 |
24456 | 1996 | 0.060317 |
I have the above data and aI want the following result
gvkey | year | RET | ret_cut_dummy |
1004 | 1992 | -0.04762 | 0 |
1004 | 1993 | 0.0138 | 0 |
1004 | 1994 | -0.00429 | 1 |
1004 | 1995 | -0.14493 | 1 |
1004 | 1996 | 0.067797 | 0 |
1004 | 1997 | 0.107302 | 0 |
1004 | 1998 | 0.072464 | 1 |
1004 | 1999 | 0.057027 | 1 |
1331 | 1992 | 0.038333 | 0 |
1331 | 1993 | -0.05405 | 1 |
1331 | 1994 | -0.04286 | 0 |
1331 | 1995 | -0.03343 | 0 |
1331 | 1996 | -0.03125 | 0 |
24456 | 1992 | -0.02621 | 0 |
24456 | 1993 | 0.006377 | 0 |
24456 | 1994 | 0 | 1 |
24456 | 1995 | 0.05 | 0 |
24456 | 1996 | 0.060317 | 0 |
So, I want dummy variable equals one if there is a reduction in annual ret, and zero otherwise. I use the following code but I am not getting 0 for the first gvkey. Though my data are sorted by gvkey and year, the result I am getting is not by year.
Data want;
set have;
by gvkey year;
if first.gvkey then ret_cut_dummy=0;
if ret < lag(ret) then ret_cut_dummy=1;
else ret_cut_dummy=0;
run;
HI @abdulla Will this help?
data have;
input gvkey year RET;
cards;
1004 1992 -0.04762
1004 1993 0.0138
1004 1994 -0.00429
1004 1995 -0.14493
1004 1996 0.067797
1004 1997 0.107302
1004 1998 0.072464
1004 1999 0.057027
1331 1992 0.038333
1331 1993 -0.05405
1331 1994 -0.04286
1331 1995 -0.03343
1331 1996 -0.03125
24456 1992 -0.02621
24456 1993 0.006377
24456 1994 0
24456 1995 0.05
24456 1996 0.060317
;
data want;
set have;
by gvkey;
if first.gvkey then ret_cut_dummy=0;
else ret_cut_dummy=RET<_iorc_;
_iorc_=ret;
run;
Your code returns your desired result?
data have;
input gvkey year RET;
datalines;
1004 1992 -0.04762
1004 1993 0.0138
1004 1994 -0.00429
1004 1995 -0.14493
1004 1996 0.067797
1004 1997 0.107302
1004 1998 0.072464
1004 1999 -0.02703
1331 1992 0.038333
1331 1993 -0.05405
1331 1994 -0.04286
1331 1995 -0.03343
1331 1996 -0.03125
24456 1992 -0.02621
24456 1993 0.006377
24456 1994 0
24456 1995 0.05
24456 1996 0.060317
;
Data want;
set have;
by gvkey year;
if first.gvkey then ret_cut_dummy=0;
if ret < lag(ret) then ret_cut_dummy=1;
else ret_cut_dummy=0;
run;
HI @abdulla Will this help?
data have;
input gvkey year RET;
cards;
1004 1992 -0.04762
1004 1993 0.0138
1004 1994 -0.00429
1004 1995 -0.14493
1004 1996 0.067797
1004 1997 0.107302
1004 1998 0.072464
1004 1999 0.057027
1331 1992 0.038333
1331 1993 -0.05405
1331 1994 -0.04286
1331 1995 -0.03343
1331 1996 -0.03125
24456 1992 -0.02621
24456 1993 0.006377
24456 1994 0
24456 1995 0.05
24456 1996 0.060317
;
data want;
set have;
by gvkey;
if first.gvkey then ret_cut_dummy=0;
else ret_cut_dummy=RET<_iorc_;
_iorc_=ret;
run;
Using LAG function under IF may result unpredictable.
Here is a slight change to @PeterClemmensen code:
proc sort data=have; by gvkey year; run;
Data want;
set have;
by gvkey ;
retain prev_ret;
if first.gvkey then do;
ret_cut_dummy=0;
prev_ret = ret;
end;
else do;
if ret < prev_ret then ret_cut_dummy=1;
else ret_cut_dummy=0;
prev_ret = ret;
end;
drop prev_ret;
run;
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.