Identify multiple treatments and time among them

[Alessandro Corvasce]

Hello everybody,
I have a dataset containing tornado occurences in the USA.

h-geocode is the block -ID;
hit==1 if a tornado hit the block during the year;
treat =[egen treat=max(hit), by( h_geocode)];
treat_yr=[gen yr_tr=year // replace yr_tr=0 if hit==0// recode yr_tr 0=.];
multi_tr=[ by h_geocode: egen multi_tr=count( yr_tr)]

Hence multi_tr helps me identify wich blocks have been hit more than once. Now I would like to determine the distance (in year) between multiple occurences at the block level (h_geocode) in order to determine the best window for an eventy study analysis of the impact of tornado (hit==1) on my Y_var.
Can you suggest me the most effective way to calculate the distance between different treatment years (treat_yr)?
Thank you!
]

* Example generated by -dataex-. For more info, type help dataex
clear
input double h_geocode float(year hit Y_var treat yr_tr multi_tr)
10010201001000 2002 0 .8644371 0 . 0
10010201001000 2003 0 .8455727 0 . 0
10010201001000 2004 0 .8488644 0 . 0
10010201001000 2005 0  .853914 0 . 0
10010201001000 2006 0 .8604227 0 . 0
10010201001000 2007 0 .8604227 0 . 0
10010201001000 2008 0  .855893 0 . 0
10010201001000 2009 0 .8528046 0 . 0
10010201001000 2010 0 .8583788 0 . 0
10010201001000 2011 0 .8745581 0 . 0
10010201001000 2012 0 .8752647 0 . 0
10010201001000 2013 0 .8741398 0 . 0
10010201001000 2014 0 .8763138 0 . 0
10010201001000 2015 0 .8528046 0 . 0
10010201001000 2016 0  .855893 0 . 0
10010201001000 2017 0 .8549405 0 . 0
10010201001000 2018 0 .8455727 0 . 0
10010201001000 2019 0 .8088669 0 . 0
10010201001002 2002 0 .4812118 0 . 0
10010201001002 2003 0        0 0 . 0
10010201001002 2004 0        0 0 . 0
10010201001002 2005 0        0 0 . 0
10010201001002 2006 0 .6931472 0 . 0
10010201001002 2007 0 .6251451 0 . 0
10010201001002 2008 0 .4812118 0 . 0
10010201001002 2009 0 .4812118 0 . 0
10010201001002 2010 0 .4812118 0 . 0
10010201001003 2002 0 .8415393 0 . 0
10010201001003 2003 0 .8488644 0 . 0
10010201001003 2004 0 .8436636 0 . 0
10010201001003 2005 0 .8502933 0 . 0
10010201001003 2006 0  .859103 0 . 0
10010201001003 2007 0 .8436636 0 . 0
10010201001003 2008 0 .8549405 0 . 0
10010201001003 2009 0 .8472978 0 . 0
10010201001003 2010 0 .8472978 0 . 0
10010201001003 2011 0 .8317574 0 . 0
10010201001003 2012 0 .8549405 0 . 0
10010201001003 2013 0 .8549405 0 . 0
10010201001003 2014 0 .8516018 0 . 0
10010201001003 2015 0  .859103 0 . 0
10010201001003 2016 0  .859783 0 . 0
10010201001003 2017 0 .8610256 0 . 0
10010201001003 2018 0 .8502933 0 . 0
10010201001003 2019 0  .859103 0 . 0
10010201001005 2012 0        0 0 . 0
10010201001005 2015 0        0 0 . 0
10010201001005 2017 0        0 0 . 0
10010201001005 2018 0        0 0 . 0
10010201001007 2002 0 .7901687 0 . 0
10010201001007 2003 0 .7326683 0 . 0
10010201001007 2004 0 .8005853 0 . 0
10010201001007 2005 0 .8212039 0 . 0
10010201001007 2006 0 .7152214 0 . 0
10010201001007 2007 0 .7584862 0 . 0
10010201001007 2008 0 .8259218 0 . 0
10010201001007 2009 0 .8156089 0 . 0
10010201001007 2010 0 .7901687 0 . 0
10010201001007 2011 0 .6251451 0 . 0
10010201001007 2012 0 .8259218 0 . 0
10010201001007 2013 0 .5688249 0 . 0
10010201001007 2014 0 .7584862 0 . 0
10010201001007 2015 0 .8156089 0 . 0
10010201001007 2016 0 .7584862 0 . 0
10010201001007 2017 0 .8005853 0 . 0
10010201001007 2018 0 .8005853 0 . 0
10010201001007 2019 0 .8005853 0 . 0
10010201001008 2002 0        0 0 . 0
10010201001008 2004 0        0 0 . 0
10010201001008 2005 0 .4812118 0 . 0
10010201001008 2006 0 .6251451 0 . 0
10010201001008 2007 0 .4812118 0 . 0
10010201001008 2008 0 .6251451 0 . 0
10010201001008 2009 0        0 0 . 0
10010201001008 2010 0 .4812118 0 . 0
10010201001009 2003 0        0 0 . 0
10010201001009 2004 0        0 0 . 0
10010201001009 2010 0        0 0 . 0
10010201001009 2012 0        0 0 . 0
10010201001009 2014 0        0 0 . 0
10010201001009 2016 0        0 0 . 0
10010201001010 2006 0        0 0 . 0
10010201001010 2010 0        0 0 . 0
10010201001011 2003 0        0 0 . 0
10010201001011 2007 0        0 0 . 0
10010201001012 2002 0 .7766703 0 . 0
10010201001012 2003 0 .4812118 0 . 0
10010201001012 2004 0 .7766703 0 . 0
10010201001012 2005 0 .6251451 0 . 0
10010201001012 2006 0 .7326683 0 . 0
10010201001012 2007 0 .7326683 0 . 0
10010201001012 2008 0 .6931472 0 . 0
10010201001012 2009 0 .7584862 0 . 0
10010201001012 2010 0 .6251451 0 . 0
10010201001012 2014 0        0 0 . 0
10010201001012 2016 0        0 0 . 0
10010201001012 2017 0        0 0 . 0
10010201001012 2018 0        0 0 . 0
10010201001012 2019 0        0 0 . 0
10010201001013 2002 0 .8156089 0 . 0
end

[Clyde Schechter]

For the purposes of answering your question, example data that doesn't contain any tornado occurrences at all (let alone a block with multiple occurrences) is not useful. Please post back with an example that is fit for purpose.

[Alessandro Corvasce]

Sorry, I didn't notice it. Here a more fit dataex

* Example generated by -dataex-. For more info, type help dataex
clear
input double h_geocode float(year hit Y_var treat yr_tr multi_tr)
10010208011000 2003 0 .6251451 1    . 2
10010208011000 2004 1 .4812118 1 2004 2
10010208011000 2005 0 .7326683 1    . 2
10010208011000 2012 0 .7901687 1    . 2
10010208011000 2015 0 .7326683 1    . 2
10010208011000 2017 0 .6931472 1    . 2
10010208011001 2005 0        0 1    . 2
10010208011001 2014 0 .7326683 1    . 2
10010208011001 2016 0 .7326683 1    . 2
10010208011001 2018 0        0 1    . 2
10010208011001 2019 0 .7766703 1    . 2
10010208011003 2002 0 .6931472 1    . 2
10010208011003 2006 0 .6931472 1    . 2
10010208011003 2008 1 .6251451 1 2008 2
10010208011003 2011 0        0 1    . 2
10010208011003 2014 0 .4812118 1    . 2
10010208011003 2015 0 .4812118 1    . 2
10010208011005 2002 0 .7766703 1    . 2
10010208011005 2005 0 .6931472 1    . 2
10010208011005 2010 0 .4812118 1    . 2
10010208011006 2002 0 .8391615 1    . 2
10010208011006 2009 0 .8488644 1    . 2
10010208011006 2010 0 .8604227 1    . 2
10010208011006 2011 0 .8567792 1    . 2
10010208011006 2017 0 .8436636 1    . 2
10010208011006 2019 0  .855893 1    . 2
10010208011008 2002 0 .7584862 1    . 2
10010208011008 2004 1 .7326683 1 2004 2
10010208011008 2010 0 .8005853 1    . 2
10010208011008 2013 0 .8212039 1    . 2
10010208011008 2016 0 .7901687 1    . 2
10010208011008 2018 0 .8005853 1    . 2
10010208011009 2005 0        0 1    . 2
10010208011009 2007 0 .6251451 1    . 2
10010208011009 2011 0 .8436636 1    . 2
10010208011009 2015 0 .8576059 1    . 2
10010208011009 2017 0 .8415393 1    . 2
10010208011009 2018 0 .8212039 1    . 2
10010208011014 2007 0 .6251451 1    . 2
10010208011014 2008 1 .6931472 1 2008 2
10010208011014 2010 0 .6931472 1    . 2
10010208011015 2003 0        0 1    . 2
10010208011015 2007 0 .7584862 1    . 2
10010208011015 2008 1 .7326683 1 2008 2
10010208011015 2009 0 .7326683 1    . 2
10010208011015 2017 0        0 1    . 2
10010208011018 2005 0 .8212039 1    . 2
10010208011018 2007 0  .833439 1    . 2
10010208011018 2010 0 .8212039 1    . 2
10010208011018 2013 0 .7901687 1    . 2
10010208011018 2014 0  .853914 1    . 2
10010208011018 2019 0 .8259218 1    . 2
10010208011020 2002 0 .7766703 1    . 2
10010208011020 2003 0 .7584862 1    . 2
10010208011020 2004 1 .7326683 1 2004 2
10010208011020 2012 0 .4812118 1    . 2
10010208011020 2014 0 .4812118 1    . 2
10010208011020 2017 0 .6931472 1    . 2
10010208011021 2002 0 .8610256 1    . 2
10010208011021 2005 0 .8615947 1    . 2
10010208011021 2011 0  .859783 1    . 2
10010208011021 2012 0 .8528046 1    . 2
10010208011021 2014 0 .8549405 1    . 2
10010208011021 2019 0 .8615947 1    . 2
10010208011022 2003 0 .8005853 1    . 2
10010208011022 2005 0 .8005853 1    . 2
10010208011022 2009 0 .6931472 1    . 2
10010208011022 2012 0 .6251451 1    . 2
10010208011022 2013 0        0 1    . 2
10010208011022 2014 0 .7326683 1    . 2
10010208011024 2002 0 .8626426 1    . 2
10010208011024 2008 1  .859783 1 2008 2
10010208011024 2011 0  .854437 1    . 2
10010208011024 2013 0 .8720389 1    . 2
10010208011024 2017 0 .8707803 1    . 2
10010208011024 2019 0 .8720389 1    . 2
10010208011027 2003 0 .6931472 1    . 2
10010208011027 2008 1 .6931472 1 2008 2
10010208011027 2014 0 .4812118 1    . 2
10010208011027 2015 0 .6931472 1    . 2
10010208011027 2017 0 .6251451 1    . 2
10010208011027 2018 0 .7901687 1    . 2
10010208011028 2003 0        0 1    . 2
10010208011028 2006 0 .4812118 1    . 2
10010208011028 2010 0 .4812118 1    . 2
10010208011029 2002 0 .8364819 1    . 2
10010208011029 2004 1 .8436636 1 2004 2
10010208011029 2010 0 .8436636 1    . 2
10010208011029 2013 0 .8436636 1    . 2
10010208011029 2015 0 .8502933 1    . 2
10010208011029 2019 0 .8583788 1    . 2
10010208011030 2006 0 .8005853 1    . 2
10010208011030 2008 1 .8088669 1 2008 2
10010208011030 2009 0 .8088669 1    . 2
10010208011030 2010 0 .7326683 1    . 2
10010208011030 2016 0 .8005853 1    . 2
10010208011030 2019 0 .7901687 1    . 2
10010208011035 2002 0 .4812118 1    . 2
10010208011035 2004 1        0 1 2004 2
10010208011035 2013 0 .4812118 1    . 2
end

[Clyde Schechter]

Well, this example contains some tornado hits, but there are no geocodes that have more than one, so there is no interval between hits to count. Nevertheless, here is some code that shows how you might do it in a data set that is suitable:

by h_geocode (year), sort: gen year_prior_hit = .
by h_geocode (year): replace year_prior_hit = ///
    cond(hit[_n-1], year[_n-1], year_prior_hit[_n-1])
gen interval_between_hits = year - year_prior_hit if hit

[Alessandro Corvasce]

thank you Clyde!
Alternatively I was thinking something like:

gen timehit= yr_tr
recode timehit .=0

xtset h_geocode year
forvalues i = 1/6 {
gen int`i'= f`i'.timehit- timehit
replace int`i'=0 if int`i' >2001
replace int`i'=0 if int`i'<0
}

[Clyde Schechter]

The code you show in #5 might work, but there is a good chance it will not. It depends on how the data play out. The limitation of your code is that it will only look a maximum of 6 years ahead. So if you have two tornadoes that are, say 10 years apart, you will not find that interval. Also, your approach will leave you with the intervals scattered among 6 different variables, and you will then have the problem of not know where to look for them when you need them, or having to check all 6 variables each time you want to find one interval.

By contrast, the code in #4 will leave you with a single new variable that, for each tornado, tells you in that observation, the interval since the preceding tornado. That will probably prove much more workable for whatever you plan to do with the information.

Added: If the point you are trying to make in #5 is that you want the interval associated with each tornado to be the interval to the next tornado, rather than since the last, then the approach in #4 can be slightly modified as:

gsort h_geocode -year
gen next_hit_year = .
by h_geocode: replace next_hit_year = cond(hit[_n-1], year[_n-1], next_hit_year[_n-1])
sort h_geocode year
gen interval = next_hit_year - year if hit

[Alessandro Corvasce]

I will stick to your suggestions, the results are more clear indeed. Thank you again