How to treat adjacent events

[Khang]

I am attaching output table here. I have already obtained Panel A and Panel B of the given table.

 Now I want to develop panel C of the given table which they called Economic events (Economic events are defined as major investments (Built1) with adjacent event years, starting with a pre-event year and ending with a post-event year (e.g., a sequence in event time of [–1, 0, 0, +1] over 4 years).
I am stuck at adjacent events How we will treat 4 years [-1,0,0,+1]

data work.new;
  infile datalines dsd truncover;
  input FYEAR:BEST12. CUSIP:$10. DEBT:BEST12. EQUITY:BEST12. CASHFLOW:BEST12. OTHER:BEST12. BUILT1:BEST12.;
datalines;
1989,000361,7.698,0,28.44,-17.723,
1990,000361,-22.256,-2.326,21.348,12.118,
1991,000361,6.873,0,13.32,-11.975,
1992,000361,-1.005,-1.164,3.547,5.947,
1993,000361,24.974,-0.066,-4.042,-14.882,
1994,000361,5.101,-0.177,8.728,-4.579,
1995,000361,-1.632,-1.552,7.332,3.399,
1996,000361,-1.474,-8.08,9.237,30.609,1
1997,000361,49.177,1.642,75.305,-80.481,1
1998,000361,2.053,-7.558,58.331,-1.52,1
1999,000361,25.401,-10.53,51.327,-43.854,
2000,000361,-26.364,-0.211,15.383,27.526,
2001,000361,50.093,34.129,-61.586,2.727,
2002,000361,-32.643,0,19.333,23.24,
2003,000361,-4.914,0,18.328,-3.128,
2004,000361,-24.005,0,37.194,-0.156,
2005,000361,140.624,0,-7.015,-117.313,1
1989,000781,8.331,0,2.291,-0.506,1
1990,000781,4.208,0,2.612,-1.327,
1991,000781,0.201,0,2.37,0.155,
1992,000781,0.423,0,4.249,0.348,
1993,000781,16.199,0,4.647,-2.124,1
1994,000781,21.157,0,6.914,-5.987,1
1989,00099V,-37.088,0,-151.117,-324.79,
1990,00099V,-57.638,0,359.763,226.468,
1991,00099V,15.556,0,71.947,-121.261,
1992,00099V,-116.575,0,109.166,95.715,
1993,00099V,-286.093,0,252.856,-329.048,
1994,00099V,63.7,0,40.4,-38.6,
1995,00099V,205.6,0,155.7,-252,1
1996,00099V,57.8,0,184.2,-124.8,
1997,00099V,582.2,15,-125.6,-373.8,
1998,00099V,-6.5,0,431.8,-323.6,
1999,00099V,172.4,0,99.6,-74.6,1
2000,00099V,84.4,0,190.2,-76.2,
2001,00099V,-118.9,45,14.1,208.6,
2002,00099V,81.8,15,273,-258.8,
2003,00099V,-143.6,0,42.4,326.4,1
1989,000872,2.711,0.001,-0.404,-0.148,
1990,000872,1.995,0.006,-1.005,0.277,
;;;;

[mkeintz]

You can make an EVENT file from work.new.  Each EVENT record could have these variables:

• CUSIP
• EVENT_NUMBER   (a running count of events within each CUSIP)
• EVENT_PRE_YEAR  (the year preceding a sequence of at least two years with BUILT1=1)
• EVENT_POST_YEAR  (the year following at least two year with BUILT1=1 preceded by a BUILT1=.).

Here's a code that will do so:

data events (keep=cusip event_number pre_event_year post_event_year);

  array hist{1987:2010} ;

  /* Build an historical array of BUILT1 values */
  do until (last.cusip);
    set work.new;
    by cusip notsorted;
    if first.cusip then cusip_start=fyear;
    if built1=. then built1=0;
    hist{fyear}=built1;
  end; 
  cusip_end=fyear;

  pre_event_year=.;

  /* Now scan the array, looking for sequences of 0 */
  /* followed by two or more 1's followed by a 0    */

  do yr=cusip_start to cusip_end;
    if hist{yr}=0 then do;  /* YR is a potential event-span start or  end */
      if pre_event_year^=. and hist{yr-1}=1 and hist{yr-2}=1 then post_event_year=yr;
      else pre_event_year=yr;
    end;
    if n(pre_event_year,post_event_year)=2 then do;
      event_number=sum(event_number,1);
      output;
      call missing(pre_event_year,post_event_year);
    end;
  end;

run;

proc freq data=events;
  tables event_number;
run;

Notes:

1. The proc freq will report both the total number of events and total number of firms having at least one event.  The frequency of EVENT_NUMBER=1 is the number of firms, and the total of all values of EVENT_NUMBER is the number of events.
2. No year is counted twice,  I.e.,, while a sequence of built1=  ., 1, 1, ., ., 1, 1, .  is two events (because there are two years with BUILT1=. in the middle), the sequence ., 1, 1, ., 1, 1, .  is a SINGLE event.