Generating counting and dummy variables from a panel

[Michael Kwadwo]

I want to generate the variables age_ma, d_1 and d_2 using the id_firm, year, and buyer

This is how it is

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id_firm int year byte buyer float(age_ma d_1 d_2)
 51 2005 0 . . .
 51 2006 0 . . .
 51 2007 0 . . .
 51 2008 0 . . .
 51 2009 0 . . .
 51 2010 0 . . .
 51 2011 0 . . .
 51 2012 0 . . .
 51 2013 0 . . .
 51 2014 0 . . .
 51 2015 0 . . .
 51 2016 1 . . .
 51 2017 0 . . .
 51 2019 0 . . .
 73 2004 0 . . .
 73 2005 0 . . .
 73 2006 1 . . .
 73 2007 1 . . .
 73 2009 1 . . .
 73 2010 0 . . .
 73 2011 0 . . .
 73 2012 0 . . .
 73 2013 1 . . .
 73 2014 1 . . .
 73 2015 0 . . .
 73 2016 0 . . .
 73 2017 0 . . .
 73 2018 0 . . .
 73 2019 0 . . .
 73 2020 0 . . .
269 2004 0 . . .
269 2005 0 . . .
269 2006 0 . . .
269 2007 1 . . .
269 2008 0 . . .
269 2009 0 . . .
269 2010 0 . . .
269 2011 0 . . .
269 2012 0 . . .
269 2013 0 . . .
269 2014 1 . . .
269 2015 1 . . .
269 2016 0 . . .
269 2017 0 . . .
269 2018 0 . . .
269 2019 0 . . .
269 2020 0 . . .
298 2013 0 . . .
298 2014 0 . . .
298 2015 0 . . .
298 2016 0 . . .
298 2017 1 . . .
298 2018 0 . . .
298 2019 0 . . .
298 2020 1 . . .
326 2004 0 . . .
326 2005 0 . . .
326 2006 1 . . .
326 2007 0 . . .
326 2008 0 . . .
326 2009 0 . . .
326 2010 0 . . .
326 2011 0 . . .
326 2013 0 . . .
326 2014 0 . . .
326 2015 0 . . .
326 2016 0 . . .
326 2017 0 . . .
end
format %ty year

but I want it to be like this, the age_ma will start counting the year after buyer is equal 1 for each panel id. d_1 will take the value of 1 a year after buyer=1 and d_2 will take a value of 1, 2 years after buyer=1

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id_firm int year byte buyer float(age_ma d_1 d_2)
 51 2005 0 . . .
 51 2006 0 . . .
 51 2007 0 . . .
 51 2008 0 . . .
 51 2009 0 . . .
 51 2010 0 . . .
 51 2011 0 . . .
 51 2012 0 . . .
 51 2013 0 . . .
 51 2014 0 . . .
 51 2015 0 . . .
 51 2016 1 . . .
 51 2017 0 1 1 .
 51 2019 0 2 . 1
 73 2004 0 . . .
 73 2005 0 . . .
 73 2006 1 . . .
 73 2007 1 1 1 .
 73 2009 1 2 1 1
 73 2010 0 3 1 1
 73 2011 0 5 . 1
 73 2012 0 6 . .
 73 2013 1 7 . .
 73 2014 1 8 1 .
 73 2015 0 9 1 1
 73 2016 0 10 . 1
 73 2017 0 11 . .
 73 2018 0 12 . .
 73 2019 0 13 . .
 73 2020 0 14 . .
269 2004 0 . . .
269 2005 0 . . .
269 2006 0 . . .
269 2007 1 . . .
269 2008 0 1 1 .
269 2009 0 2 . 1
269 2010 0 3 . .
269 2011 0 4 . .
269 2012 0 5 . .
269 2013 0 6 . .
269 2014 1 7 . .
269 2015 1 8 1 .
269 2016 0 9 1 1
269 2017 0 10 . 1
269 2018 0 11 . .
269 2019 0 12 . .
269 2020 0 13 . .
end
format %ty year

[Nick Cox]

Your example doesn't seem consistent -- note that for identifier 73 the age variable jumps from 3 to 5 in successive years. Also, your data have gaps.

This may help nevertheless. But note that because you have gaps, there are problems with indicators for age equal to 1 or 2.

Perhaps you need to tsfill your dataset.

 Example generated by -dataex-. To install: ssc install dataex
clear
input float id_firm int year byte buyer float(age_ma d_1 d_2)
 51 2005 0 . . .
 51 2006 0 . . .
 51 2007 0 . . .
 51 2008 0 . . .
 51 2009 0 . . .
 51 2010 0 . . .
 51 2011 0 . . .
 51 2012 0 . . .
 51 2013 0 . . .
 51 2014 0 . . .
 51 2015 0 . . .
 51 2016 1 . . .
 51 2017 0 . . .
 51 2019 0 . . .
 73 2004 0 . . .
 73 2005 0 . . .
 73 2006 1 . . .
 73 2007 1 . . .
 73 2009 1 . . .
 73 2010 0 . . .
 73 2011 0 . . .
 73 2012 0 . . .
 73 2013 1 . . .
 73 2014 1 . . .
 73 2015 0 . . .
 73 2016 0 . . .
 73 2017 0 . . .
 73 2018 0 . . .
 73 2019 0 . . .
 73 2020 0 . . .
269 2004 0 . . .
269 2005 0 . . .
269 2006 0 . . .
269 2007 1 . . .
269 2008 0 . . .
269 2009 0 . . .
269 2010 0 . . .
269 2011 0 . . .
269 2012 0 . . .
269 2013 0 . . .
269 2014 1 . . .
269 2015 1 . . .
269 2016 0 . . .
269 2017 0 . . .
269 2018 0 . . .
269 2019 0 . . .
269 2020 0 . . .
298 2013 0 . . .
298 2014 0 . . .
298 2015 0 . . .
298 2016 0 . . .
298 2017 1 . . .
298 2018 0 . . .
298 2019 0 . . .
298 2020 1 . . .
326 2004 0 . . .
326 2005 0 . . .
326 2006 1 . . .
326 2007 0 . . .
326 2008 0 . . .
326 2009 0 . . .
326 2010 0 . . .
326 2011 0 . . .
326 2013 0 . . .
326 2014 0 . . .
326 2015 0 . . .
326 2016 0 . . .
326 2017 0 . . .
end
format %ty year

bysort id : egen when_buyer = min(cond(buyer == 1, year, .))
replace age_ma = year - when_buyer if year > when_buyer 

list, sepby(id_firm) 

    +--------------------------------------------------------+
     | id_firm   year   buyer   age_ma   d_1   d_2   when_b~r |
     |--------------------------------------------------------|
  1. |      51   2005       0        .     .     .       2016 |
  2. |      51   2006       0        .     .     .       2016 |
  3. |      51   2007       0        .     .     .       2016 |
  4. |      51   2008       0        .     .     .       2016 |
  5. |      51   2009       0        .     .     .       2016 |
  6. |      51   2010       0        .     .     .       2016 |
  7. |      51   2011       0        .     .     .       2016 |
  8. |      51   2012       0        .     .     .       2016 |
  9. |      51   2013       0        .     .     .       2016 |
 10. |      51   2014       0        .     .     .       2016 |
 11. |      51   2015       0        .     .     .       2016 |
 12. |      51   2016       1        .     .     .       2016 |
 13. |      51   2017       0        1     .     .       2016 |
 14. |      51   2019       0        3     .     .       2016 |
     |--------------------------------------------------------|
 15. |      73   2004       0        .     .     .       2006 |
 16. |      73   2005       0        .     .     .       2006 |
 17. |      73   2006       1        .     .     .       2006 |
 18. |      73   2007       1        1     .     .       2006 |
 19. |      73   2009       1        3     .     .       2006 |
 20. |      73   2010       0        4     .     .       2006 |
 21. |      73   2011       0        5     .     .       2006 |
 22. |      73   2012       0        6     .     .       2006 |
 23. |      73   2013       1        7     .     .       2006 |
 24. |      73   2014       1        8     .     .       2006 |
 25. |      73   2015       0        9     .     .       2006 |
 26. |      73   2016       0       10     .     .       2006 |
 27. |      73   2017       0       11     .     .       2006 |
 28. |      73   2018       0       12     .     .       2006 |
 29. |      73   2019       0       13     .     .       2006 |
 30. |      73   2020       0       14     .     .       2006 |
     |--------------------------------------------------------|
 31. |     269   2004       0        .     .     .       2007 |
 32. |     269   2005       0        .     .     .       2007 |
 33. |     269   2006       0        .     .     .       2007 |
 34. |     269   2007       1        .     .     .       2007 |
 35. |     269   2008       0        1     .     .       2007 |
 36. |     269   2009       0        2     .     .       2007 |
 37. |     269   2010       0        3     .     .       2007 |
 38. |     269   2011       0        4     .     .       2007 |
 39. |     269   2012       0        5     .     .       2007 |
 40. |     269   2013       0        6     .     .       2007 |
 41. |     269   2014       1        7     .     .       2007 |
 42. |     269   2015       1        8     .     .       2007 |
 43. |     269   2016       0        9     .     .       2007 |
 44. |     269   2017       0       10     .     .       2007 |
 45. |     269   2018       0       11     .     .       2007 |
 46. |     269   2019       0       12     .     .       2007 |
 47. |     269   2020       0       13     .     .       2007 |
     |--------------------------------------------------------|
 48. |     298   2013       0        .     .     .       2017 |
 49. |     298   2014       0        .     .     .       2017 |
 50. |     298   2015       0        .     .     .       2017 |
 51. |     298   2016       0        .     .     .       2017 |
 52. |     298   2017       1        .     .     .       2017 |
 53. |     298   2018       0        1     .     .       2017 |
 54. |     298   2019       0        2     .     .       2017 |
 55. |     298   2020       1        3     .     .       2017 |
     |--------------------------------------------------------|
 56. |     326   2004       0        .     .     .       2006 |
 57. |     326   2005       0        .     .     .       2006 |
 58. |     326   2006       1        .     .     .       2006 |
 59. |     326   2007       0        1     .     .       2006 |
 60. |     326   2008       0        2     .     .       2006 |
 61. |     326   2009       0        3     .     .       2006 |
 62. |     326   2010       0        4     .     .       2006 |
 63. |     326   2011       0        5     .     .       2006 |
 64. |     326   2013       0        7     .     .       2006 |
 65. |     326   2014       0        8     .     .       2006 |
 66. |     326   2015       0        9     .     .       2006 |
 67. |     326   2016       0       10     .     .       2006 |
 68. |     326   2017       0       11     .     .       2006 |
     +--------------------------------------------------------+

[Michael Kwadwo]

Hi @Nick Thank you.

It worked.


Also, could you assist in the code for generating d_1 and d_2

[Nick Cox]

See my paper on indicator variables with Clyde Schechter.

(0. 1) indicators are much more useful than (1, .) indicators.

gen d_1 = wanted == 1