Observations in pre and post events only

[amanjot_42]

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

[PeterClemmensen · Accepted solution]

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;

[amanjot_42]

Thank you so much!

It worked really well!

Regards

[novinosrin]

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;

[novinosrin]

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;

[amanjot_42]

thank you for the additional inputs!

Regards