Guarantee 3 consecutive observations before and after the event

[Alberto Alvarez]

Dear Stata Users,

I need to keep just those firms (gvkey) that have 3 year consecutive observations before and after the event. The event date observation is supposed to be accounted for "after" event, but not for the consecutive years of "before" event. For example, in the data below firms (gvkey) 1173 and 1266 satisfy this requirement. Please, help me with this issue.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long gvkey_destr int fyear float event
1121 2006 .
1121 2007 .
1121 2008 1
1121 2009 .
1121 2010 .
1121 2011 .
1121 2012 .
1121 2013 .
1173 2006 .
1173 2007 .
1173 2008 .
1173 2009 1
1173 2010 .
1173 2011 .
1173 2012 .
1210 2006 .
1210 2007 .
1210 2008 .
1210 2009 .
1210 2010 .
1210 2011 .
1210 2012 1
1210 2013 .
1266 2006 .
1266 2007 .
1266 2008 .
1266 2009 .
1266 2010 .
1266 2011 1
1266 2012 .
1266 2013 .
end

[David Benson]

See if this works for you:

sort gvkey fyear
bysort gvkey (event fyear): gen event_yr = fyear[1]
sort gvkey fyear
gen event_time = fyear - event_yr

egen has_event = total(event), by(gvkey)  // makes it 1 if firm ever has event
bysort gvkey (fyear): egen max_year = max(event_time)
bysort gvkey (fyear): egen min_year = min(event_time)
gen to_keep = 1 if has_event==1 & max_year>=2 & min_year<=-3  // adjust max_year and min_year as necessary
// The above should account that event date obs is supposed to be accounted for "after" event, but not for the consecutive years of "before" event

. list, sepby( gvkey_destr) noobs abbrev(12)

  +-------------------------------------------------------------------------------------------------+
  | gvkey_destr   fyear   event   event_yr   event_time   has_event   max_year   min_year   to_keep |
  |-------------------------------------------------------------------------------------------------|
  |        1121    2006       .       2008           -2           1          5         -2         . |
  |        1121    2007       .       2008           -1           1          5         -2         . |
  |        1121    2008       1       2008            0           1          5         -2         . |
  |        1121    2009       .       2008            1           1          5         -2         . |
  |        1121    2010       .       2008            2           1          5         -2         . |
  |        1121    2011       .       2008            3           1          5         -2         . |
  |        1121    2012       .       2008            4           1          5         -2         . |
  |        1121    2013       .       2008            5           1          5         -2         . |
  |-------------------------------------------------------------------------------------------------|
  |        1173    2006       .       2009           -3           1          3         -3         1 |
  |        1173    2007       .       2009           -2           1          3         -3         1 |
  |        1173    2008       .       2009           -1           1          3         -3         1 |
  |        1173    2009       1       2009            0           1          3         -3         1 |
  |        1173    2010       .       2009            1           1          3         -3         1 |
  |        1173    2011       .       2009            2           1          3         -3         1 |
  |        1173    2012       .       2009            3           1          3         -3         1 |
  |-------------------------------------------------------------------------------------------------|
  |        1210    2006       .       2012           -6           1          1         -6         . |
  |        1210    2007       .       2012           -5           1          1         -6         . |
  |        1210    2008       .       2012           -4           1          1         -6         . |
  |        1210    2009       .       2012           -3           1          1         -6         . |
  |        1210    2010       .       2012           -2           1          1         -6         . |
  |        1210    2011       .       2012           -1           1          1         -6         . |
  |        1210    2012       1       2012            0           1          1         -6         . |
  |        1210    2013       .       2012            1           1          1         -6         . |
  |-------------------------------------------------------------------------------------------------|
  |        1266    2006       .       2011           -5           1          2         -5         1 |
  |        1266    2007       .       2011           -4           1          2         -5         1 |
  |        1266    2008       .       2011           -3           1          2         -5         1 |
  |        1266    2009       .       2011           -2           1          2         -5         1 |
  |        1266    2010       .       2011           -1           1          2         -5         1 |
  |        1266    2011       1       2011            0           1          2         -5         1 |
  |        1266    2012       .       2011            1           1          2         -5         1 |
  |        1266    2013       .       2011            2           1          2         -5         1 |
  +-------------------------------------------------------------------------------------------------+

.