topic Re: Observations in pre and post events only in SAS Programming

Observations in pre and post events only

amanjot_42 — Mon, 20 May 2019 12:05:03 GMT

Hi guys,

I am new to SAS!

Actually, I was trying to limit my sample into those firms only which have data for both pre and post events in my sample. The event years are different for different firms. For instance, if event year of one firm is 2006, I want to see whether this firm has data for the previous five years and post five years or not. I don't want the firms to be having data for each of the pre-and post years. eve observation from pre- and one from post- would suffice my purpose. But they should have at least one in each if they don't then I want to delete those firms from my sample.

Data look like:

Cusip Year Sales event_year

11000 2005 10 0

11000 2006 20 1

11000 2007 30 0

11000 2008 40 0

12000 2004 65 1

12000 2005 90 0

12000 2006 65 0

13000 2010 80 0

13000 2011 75 0

13000 2012 95 1

14000 2008 65 0

14000 2009 45 0

14000 2010 70 1

14000 2011 90 0

So according to the criteria, I don't want cusip codes 13000 and 12000 because they don't have data for either pre- or post event years. Please help me with the code!

Regards

Aman

Re: Observations in pre and post events only

PeterClemmensen — Mon, 20 May 2019 13:12:34 GMT

data have;
input Cusip Year Sales dummy;
datalines;
11000 2005 10 0
11000 2006  20 1
11000 2007  30 0
11000 2008  40 0
12000 2004  65 1
12000 2005  90 0
12000 2006  65 0
13000 2010  80 0
13000 2011  75 0
13000 2012  95 1
14000 2008  65 0
14000 2009  45 0
14000 2010  70 1
14000 2011  90 0
;

data temp;
   do until (last.Cusip);
      set have;
      by Cusip;
      if dummy=1 then _year=Year;
   end;

   do until (last.Cusip);
      set have;
      by Cusip;
      if _year-5 le Year le _year+5 then output;
   end;
run;

proc sql;
   create table want as
      select * from temp
      group by Cusip
      having max(Year) gt _year
         and min(Year) lt _year;
quit;

Re: Observations in pre and post events only

novinosrin — Mon, 20 May 2019 14:15:49 GMT

Hi @amanjot_42 For what it;s worth, some boring proc sql boolean


data have;
input Cusip Year Sales dummy;
datalines;
11000 2005 10 0
11000 2006  20 1
11000 2007  30 0
11000 2008  40 0
12000 2004  65 1
12000 2005  90 0
12000 2006  65 0
13000 2010  80 0
13000 2011  75 0
13000 2012  95 1
14000 2008  65 0
14000 2009  45 0
14000 2010  70 1
14000 2011  90 0
;

proc sql;
create table want(drop=_:) as
select *,sum(_t1)>0 and sum(_t2)>0 as _t
from 
(select *,max((dummy=1)*year) as _year, calculated _year-5 lt Year lt calculated _year as _t1,calculated _Year lt year lt  calculated _year+5 as _t2
from have
group by cusip)
group by cusip
having _t;
quit;

Or even simpler

proc sql;
create table want as
select a.*
from have a left join (select cusip,year from have where dummy=1) b
on a.cusip=b.cusip
group by a.cusip
having max(b.year-5 lt a.Year lt b.year)>0 and max( b.Year lt a.year lt   b.year+5 )>0;
quit;

Re: Observations in pre and post events only

novinosrin — Mon, 20 May 2019 13:56:10 GMT

Hello @amanjot_42 Another one step DOW approach


data have;
input Cusip Year Sales dummy;
datalines;
11000 2005 10 0
11000 2006  20 1
11000 2007  30 0
11000 2008  40 0
12000 2004  65 1
12000 2005  90 0
12000 2006  65 0
13000 2010  80 0
13000 2011  75 0
13000 2012  95 1
14000 2008  65 0
14000 2009  45 0
14000 2010  70 1
14000 2011  90 0
;

data want;
do until(last.cusip);
merge have have(where=(_d=1) rename=(dummy=_d year=_year) keep=cusip dummy year);
by cusip;
if _year-5 lt Year lt _year then k=sum(k,1);
else  if  _Year lt year lt  _year+5 then k1=sum(k1,1);
end;
do until(last.cusip);
set have;
by cusip;
if k>0 and k1>0 then output;
end;
drop _: k:;
run;

Re: Observations in pre and post events only

amanjot_42 — Tue, 21 May 2019 00:33:46 GMT

Thank you so much!

It worked really well!

Regards

Re: Observations in pre and post events only

amanjot_42 — Tue, 21 May 2019 00:34:23 GMT

thank you for the additional inputs!

Regards