Retain only -3 and +3 period obs

[hkim3677]

Hi,

I need to retain only -3 and +3 period observations for each group.

Now my data looks like...

Data example;

input firm manager $ year turnover post_turnover turnover_year;

datalines;

1001 abc 2005 0 0 .

1001 abc 2006 0 0 .

1001 abc 2007 1 1 2007

1001 abc 2008 0 0 .

1001 abc 2008 0 1 .

1001 abc 2009 0 0 .

1001 abc 2009 0 1 . 

1001 abc 2010 0 0 . 

1001 abc 2010 0 1 .

1001 abc 2011 0 0 . 

1001 abc 2011 0 1 .

1001 abc 2012 1 1 2012

1001 abc 2013 0 1 .

1002 def 1990 0 0 .

1002 def 1991 0 0 .

1002 def 1992 0 0 .

1002 def 1993 0 0 .

1002 def 1994 1 1 1994

1002 def 1995 0 0 .

1002 def 1995 0 1 .

1002 def 1996 1 1 1996

1002 def 1996 0 1 .

;

run;

As you can see, I have multiple turnovers by each firm. So, observations between two turnovers were duplicated because they can be both post and pre. 

Now my problem is how I can retain only observations within three years around turnover_year (i.e., -3 and +3) for each firm. Also, I need to exclude the observations where the turnover was occurred.

Please help me this problem.

Thank you SAS masters!

[ballardw · Accepted solution]

You don't say if Manager plays any role in the selection process.

See if this gets close:

proc sql;
   create table want as
   select distinct b.*
   from (select * from example
         where not missing(turnover_year) )as a
        left join
        example as b
        on a.firm=b.firm
        and abs (a.year - b.year) le 3
        and missing(b.turnover_year)
   ;
quit;

[hkim3677]

Amazing Thank you!

[Ksharp]

Data example;
input firm manager $ year turnover post_turnover turnover_year;
datalines;
1001 abc 2005 0 0 .
1001 abc 2006 0 0 .
1001 abc 2007 1 1 2007
1001 abc 2008 0 0 .
1001 abc 2008 0 1 .
1001 abc 2009 0 0 .
1001 abc 2009 0 1 . 
1001 abc 2010 0 0 . 
1001 abc 2010 0 1 .
1001 abc 2011 0 0 . 
1001 abc 2011 0 1 .
1001 abc 2012 1 1 2012
1001 abc 2013 0 1 .
1002 def 1990 0 0 .
1002 def 1991 0 0 .
1002 def 1992 0 0 .
1002 def 1993 0 0 .
1002 def 1994 1 1 1994
1002 def 1995 0 0 .
1002 def 1995 0 1 .
1002 def 1996 1 1 1996
1002 def 1996 0 1 .
;
run;
data key;
 set example(where=(turnover_year is not missing));
 do i=turnover_year-3 to turnover_year+3;
  year=i;output;
 end;
 keep firm manager year;
run;
data want;
 if _n_=1 then do;
   if 0 then set key;
   declare hash h(dataset:'key');
   h.definekey('firm', 'manager', 'year');
   h.definedone();
 end;
set example;
if h.check()=0;
run;

proc print;run;