Creating dummy variable

[Abdan Syakura]

Dear all,

I have a broad data manipulation question that I haven't been able to solve. I want to create a dummy variable (called "surge") equal to one when variable "condition1" is equal to one, but only if eventually "condition2" also reaches one during the run. In the data sample below, for Botswana 2004-08, "surge" should be one, while for 2017-2019, "surge" should be zero because condition2 is zero between 2017-19. This is actually related to capital inflow surges literature in finance, where a surge episode is defined as capital inflow more than one standard deviation (condition 1) only if eventually it crosses the two standard deviation threshold (condition 2). I really appreciate your help. Thanks!

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 country int year byte(condition1 condition2)
"Botswana" 2000 0 0
"Botswana" 2001 0 0
"Botswana" 2002 0 0
"Botswana" 2003 0 0
"Botswana" 2004 1 0
"Botswana" 2005 1 0
"Botswana" 2006 1 1
"Botswana" 2007 1 1
"Botswana" 2008 1 0
"Botswana" 2009 0 0
"Botswana" 2010 0 0
"Botswana" 2011 0 0
"Botswana" 2012 1 0
"Botswana" 2013 1 1
"Botswana" 2014 1 0
"Botswana" 2015 1 0
"Botswana" 2016 0 0
"Botswana" 2017 1 0
"Botswana" 2018 1 0
"Botswana" 2019 1 0
"Botswana" 2020 0 0
"Cambodia" 2000 0 0
"Cambodia" 2001 0 0
"Cambodia" 2002 0 0
"Cambodia" 2003 0 0
"Cambodia" 2004 1 1
"Cambodia" 2005 1 0
"Cambodia" 2006 1 1
"Cambodia" 2007 1 1
"Cambodia" 2008 1 0
"Cambodia" 2009 0 0
"Cambodia" 2010 0 0
"Cambodia" 2011 0 0
"Cambodia" 2012 1 0
"Cambodia" 2013 0 0
"Cambodia" 2014 1 1
"Cambodia" 2015 1 0
"Cambodia" 2016 0 0
"Cambodia" 2017 1 0
"Cambodia" 2018 1 0
"Cambodia" 2019 1 0
"Cambodia" 2020 0 0
end

Best regards,

Abdan

[George Ford]

egen cid = group(country)
xtset cid year
bys cid: g group = _n
replace group = l.group if condition1 == l.condition1
bys country group: egen condition2app = mean(condition2)
g surge = condition2app > 0

[George Ford]

could use year for group

egen cid = group(country)
xtset cid year
g group = year
replace group = l.group if condition1 == l.condition1
bys country group: egen condition2app = mean(condition2)
g surge = condition2app > 0

[Abdan Syakura]

Thank you very much, George. I spent hours on this!

[George Ford]

The trick is to create the running variable for spells of 0 and 1. There may be a cleaner way to do it, but this works.

[Nick Cox]

Is this another way to do it?

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 country int year byte(condition1 condition2)
"Botswana" 2000 0 0
"Botswana" 2001 0 0
"Botswana" 2002 0 0
"Botswana" 2003 0 0
"Botswana" 2004 1 0
"Botswana" 2005 1 0
"Botswana" 2006 1 1
"Botswana" 2007 1 1
"Botswana" 2008 1 0
"Botswana" 2009 0 0
"Botswana" 2010 0 0
"Botswana" 2011 0 0
"Botswana" 2012 1 0
"Botswana" 2013 1 1
"Botswana" 2014 1 0
"Botswana" 2015 1 0
"Botswana" 2016 0 0
"Botswana" 2017 1 0
"Botswana" 2018 1 0
"Botswana" 2019 1 0
"Botswana" 2020 0 0
"Cambodia" 2000 0 0
"Cambodia" 2001 0 0
"Cambodia" 2002 0 0
"Cambodia" 2003 0 0
"Cambodia" 2004 1 1
"Cambodia" 2005 1 0
"Cambodia" 2006 1 1
"Cambodia" 2007 1 1
"Cambodia" 2008 1 0
"Cambodia" 2009 0 0
"Cambodia" 2010 0 0
"Cambodia" 2011 0 0
"Cambodia" 2012 1 0
"Cambodia" 2013 0 0
"Cambodia" 2014 1 1
"Cambodia" 2015 1 0
"Cambodia" 2016 0 0
"Cambodia" 2017 1 0
"Cambodia" 2018 1 0
"Cambodia" 2019 1 0
"Cambodia" 2020 0 0
end

bysort country (year) : gen spell_1 = sum(condition1 != condition1[_n-1])

bysort country spell_1 : egen max_2 = max(condition2)

bysort country year : gen wanted = condition1 == 1 & max_2 == 1 

list, sepby(country spell_1)


     +------------------------------------------------------------------+
     |  country   year   condit~1   condit~2   spell_1   max_2   wanted |
     |------------------------------------------------------------------|
  1. | Botswana   2000          0          0         1       0        0 |
  2. | Botswana   2001          0          0         1       0        0 |
  3. | Botswana   2002          0          0         1       0        0 |
  4. | Botswana   2003          0          0         1       0        0 |
     |------------------------------------------------------------------|
  5. | Botswana   2004          1          0         2       1        1 |
  6. | Botswana   2005          1          0         2       1        1 |
  7. | Botswana   2006          1          1         2       1        1 |
  8. | Botswana   2007          1          1         2       1        1 |
  9. | Botswana   2008          1          0         2       1        1 |
     |------------------------------------------------------------------|
 10. | Botswana   2009          0          0         3       0        0 |
 11. | Botswana   2010          0          0         3       0        0 |
 12. | Botswana   2011          0          0         3       0        0 |
     |------------------------------------------------------------------|
 13. | Botswana   2012          1          0         4       1        1 |
 14. | Botswana   2013          1          1         4       1        1 |
 15. | Botswana   2014          1          0         4       1        1 |
 16. | Botswana   2015          1          0         4       1        1 |
     |------------------------------------------------------------------|
 17. | Botswana   2016          0          0         5       0        0 |
     |------------------------------------------------------------------|
 18. | Botswana   2017          1          0         6       0        0 |
 19. | Botswana   2018          1          0         6       0        0 |
 20. | Botswana   2019          1          0         6       0        0 |
     |------------------------------------------------------------------|
 21. | Botswana   2020          0          0         7       0        0 |
     |------------------------------------------------------------------|
 22. | Cambodia   2000          0          0         1       0        0 |
 23. | Cambodia   2001          0          0         1       0        0 |
 24. | Cambodia   2002          0          0         1       0        0 |
 25. | Cambodia   2003          0          0         1       0        0 |
     |------------------------------------------------------------------|
 26. | Cambodia   2004          1          1         2       1        1 |
 27. | Cambodia   2005          1          0         2       1        1 |
 28. | Cambodia   2006          1          1         2       1        1 |
 29. | Cambodia   2007          1          1         2       1        1 |
 30. | Cambodia   2008          1          0         2       1        1 |
     |------------------------------------------------------------------|
 31. | Cambodia   2009          0          0         3       0        0 |
 32. | Cambodia   2010          0          0         3       0        0 |
 33. | Cambodia   2011          0          0         3       0        0 |
     |------------------------------------------------------------------|
 34. | Cambodia   2012          1          0         4       0        0 |
     |------------------------------------------------------------------|
 35. | Cambodia   2013          0          0         5       0        0 |
     |------------------------------------------------------------------|
 36. | Cambodia   2014          1          1         6       1        1 |
 37. | Cambodia   2015          1          0         6       1        1 |
     |------------------------------------------------------------------|
 38. | Cambodia   2016          0          0         7       0        0 |
     |------------------------------------------------------------------|
 39. | Cambodia   2017          1          0         8       0        0 |
 40. | Cambodia   2018          1          0         8       0        0 |
 41. | Cambodia   2019          1          0         8       0        0 |
     |------------------------------------------------------------------|
 42. | Cambodia   2020          0          0         9       0        0 |
     +------------------------------------------------------------------+