Calculating the length

[River Huang]

Dear All, I was asked this question. First, the data is

* Example generated by -dataex-. To install: ssc install dataex
clear
input long code int year double inve3 float(medianinve3 spike)
2 2000   .011212207889467666 .0026942156 1
2 2001   .004652664322401818 .0026942156 0
2 2002  .0063057824872456165 .0026942156 1
2 2003   .003929370970468741 .0026942156 0
2 2004  .0021902371095429227 .0026942156 0
2 2005   .002157089236485528 .0026942156 0
2 2006    .00280404201049823 .0026942156 0
2 2007  .0025765438461223937 .0026942156 0
2 2008  .0018055175281241453 .0026942156 0
2 2009   .005857645680091422 .0026942156 1
2 2010   .001214716774069126 .0026942156 0
2 2011   .000883029842004046 .0026942156 0
2 2012 .00039774813684472204 .0026942156 0
2 2013  .0050904941091668736 .0026942156 0
2 2014  .0036009324302514667 .0026942156 0
2 2015  .0033748017269586757 .0026942156 0
2 2016   .002584389329396051 .0026942156 0
2 2017   .002025560938915098 .0026942156 0
4 2000   .027700292071658875  .022264717 0
4 2001   .035246372446361925  .022264717 0
4 2002   .022780405552736704  .022264717 0
4 2003    .01775822776250611  .022264717 0
4 2006   .009510167325045489  .022264717 0
4 2009   .006243714789496335  .022264717 0
4 2010    .12440163880059046  .022264717 1
4 2011    .03739443633018239  .022264717 0
4 2012    .09511299375377695  .022264717 1
4 2013   .008926191397906697  .022264717 0
4 2014    .03314476897651029  .022264717 0
4 2015   .010371283584058222  .022264717 0
4 2016   .010034764879193006  .022264717 0
4 2017    .02174902999552444  .022264717 0
5 2006  .0006019977669674259  .006625881 0
5 2007  .0008470770404338991  .006625881 0
5 2008   .004311784985972088  .006625881 0
5 2011   .007327270066669626  .006625881 0
5 2012    .01432647805443116  .006625881 1
5 2013   .008980868845271842  .006625881 0
5 2014    .21303528572685007  .006625881 1
5 2016  .0061001127192159945  .006625881 0
5 2017   .006625881166223293  .006625881 0
6 2000   .012802010792224068   .01280201 0
6 2001    .08606658218795088   .01280201 1
6 2002     .0755770433061105   .01280201 1
6 2004     .0030340841144267   .01280201 0
6 2005    .01762134072401256   .01280201 0
6 2006       .25411283184605   .01280201 1
6 2007  .0010882708206108927   .01280201 0
6 2008   .002493299631683462   .01280201 0
6 2009  .0008146183401212614   .01280201 0
end

In particular, codes denote firms, `inve3' is the investment ratio. In addition, `medianinve3' is the median investment and `spike' (investment spike) is defined as

bys code: egen medianinve3 = median(inve3)
gen spike = (inve3 > 2*medianinve3)

The purpose is to calculate `Spell Lengthit' which is our proxy for investment timing and is defined as `the number of years' since firm i’s last investment spike. Any suggestions? Thanks.

[Clyde Schechter]

by code (year), sort: gen spell = sum(spike)
by code spell (year), sort: gen SpellLengthit = _n - 1 if spell > 0

[River Huang]

Thank you for this helpful suggestion, Clyde.