Creating variables with Pre/Post time windows

[Christelle Alkhoury]

Hello,

I want to create 2 variables based on the pre/post period of another variable (i.e., Breached_firm).
Specifically, I want to create a Pre_Breach variable which takes the value of 1 if a breach occured in the 3 years window leading up to the breach date (so if Breached_firm=1 in 2020, Pre_Breach is coded 1 for 2017, 2018, 2019).
Also, I want to create a Post_Breach variable which takes the value of 1 if a breach occured in the 3 years window following the breach date (so if Breached_firm=1 in 2020, Post_Breach is coded 1 for 2021, 2022 ,2023).

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Year long companyid float Breached_firm
2019  55 0
2019  55 0
2019  55 0
2019  55 0
2019  55 0
2019  55 0
2019  55 0
2019  55 0
2019  55 0
2007 147 1
2007 147 1
2007 147 1
2007 147 1
2007 147 1
2007 147 1
2007 147 1
2007 147 1
2007 147 1
2007 147 1
2008 147 0
2008 147 0
2008 147 0
2008 147 0
2008 147 0
2008 147 0
2008 147 0
2008 147 0
2008 147 0
2008 147 0
2009 147 0
2009 147 0
2009 147 0
2009 147 0
2009 147 0
2009 147 0
2009 147 0
2009 147 0
2009 147 0
2009 147 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
end

For example, for companyid 147 the breach happened in 2007, so the Pre_Breach would =1 in 2006, 2005 and 2004. And the Post_Breach=1 in 2008, 2009 and 2010.

P.S. I am using Stata/SE 16.0.

Thank you!

[Andrew Musau]

You need to convert to a panel. Then you can merge back after creating the indicators.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Year long companyid float Breached_firm
2019  55 0
2019  55 0
2019  55 0
2019  55 0
2019  55 0
2019  55 0
2019  55 0
2019  55 0
2019  55 0
2007 147 1
2007 147 1
2007 147 1
2007 147 1
2007 147 1
2007 147 1
2007 147 1
2007 147 1
2007 147 1
2007 147 1
2008 147 0
2008 147 0
2008 147 0
2008 147 0
2008 147 0
2008 147 0
2008 147 0
2008 147 0
2008 147 0
2008 147 0
2009 147 0
2009 147 0
2009 147 0
2009 147 0
2009 147 0
2009 147 0
2009 147 0
2009 147 0
2009 147 0
2009 147 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2008 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2010 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
2011 177 0
end

frame put *, into(panel)
frame panel{
    contract *, nomiss
    expand 7 if Breached_firm, gen(new)
    bys companyid Year: replace Breached_firm=!Breached_firm if new
    bys companyid Year (Breached_firm): gen pre= new[1] & _n<=3
    by companyid Year (Breached_firm): replace Year= cond(_n==1, Year[1]-3, Year[_n-1]+1) if new[1] & _n<=3
    bys companyid Year (Breached_firm): gen post= new[1] & Breached_firm[_N]& _n<=3
    by companyid Year (Breached_firm): replace Year= Year+_n if new[1] & Breached_firm[_N]& _n<=3
    bys companyid Year (new): keep if (_n==_N & new) |  _N==1
    drop _freq new
}
frame panel: list, sepby(company)

Res.:

. frame panel: list, sepby(company)

     +-----------------------------------------+
     | Year   compan~d   Breach~m   pre   post |
     |-----------------------------------------|
  1. | 2019         55          0     0      0 |
     |-----------------------------------------|
  2. | 2004        147          0     1      0 |
  3. | 2005        147          0     1      0 |
  4. | 2006        147          0     1      0 |
  5. | 2007        147          1     0      0 |
  6. | 2008        147          0     0      1 |
  7. | 2009        147          0     0      1 |
  8. | 2010        147          0     0      1 |
     |-----------------------------------------|
  9. | 2008        177          0     0      0 |
 10. | 2010        177          0     0      0 |
 11. | 2011        177          0     0      0 |
     +-----------------------------------------+

[Christelle Alkhoury]

Thank you Andrew for your reply.

I got the below error:

[P] error . . . . . . . . . . . . . . . . . . . . . . . . Return code 2000
no observations
You have requested some statistical calculation and there are
no observations on which to perform it. Perhaps you specified
if or in and inadvertently filtered all the data.

(end of search)

Any idea why the command did not work?

Thank you!

[Andrew Musau]

You need to show the full Stata output - i.e., both commands and results. Post these within CODE delimiters.