topic dummy variable for reduction in return in SAS Programming

dummy variable for reduction in return

abdulla — Mon, 23 Mar 2020 13:30:53 GMT

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;

Re: dummy variable for reduction in return

PeterClemmensen — Mon, 23 Mar 2020 13:25:24 GMT

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;

Re: dummy variable for reduction in return

abdulla — Mon, 23 Mar 2020 13:33:32 GMT

I am sorry. I have edited the data slightly. Now I am not getting what I want. If you look at the first gvkey 1331 the dummy should be 0, but I am getting 1.

Re: dummy variable for reduction in return

novinosrin — Mon, 23 Mar 2020 13:42:37 GMT

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;

Re: dummy variable for reduction in return

abdulla — Mon, 23 Mar 2020 13:48:55 GMT

Thanks novinosrin. It works

Re: dummy variable for reduction in return

Shmuel — Mon, 23 Mar 2020 13:51:52 GMT

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;