creating a variable by adding and subtracting consecutive values

[Sujin Shin]

Hello, I have a question in creating a variable in panel data. I have attached a part of my data below.

What I want to do is to create a generate ct_age1 = c_age1 that displays consecutive numbers within the same h_pid group.

For example, I want something like:

input long h_pid float(year age ct_age1 c_age2 c_age3)
22502 2005 26 -2 . .
22502 2006 27 -1 . .
22502 2007 28 0 . .
22502 2008 29 1 . .
22502 2009 30 2 . .
22502 2010 31 3 . .
22502 2011 32 4 . .
22502 2012 33 5 . .
22502 2013 34 6 . .
22502 2014 35 7 . .
22502 2015 36 8 . .
22502 2016 37 9 . .
22502 2017 38 10 . .
22502 2018 39 11 . .
22502 2019 40 12 . .
22502 2020 41 13 . .

I'd be appreciated if you can help me to generate the variable. Thank you.

* Example generated by -dataex-. For more info, type help dataex
clear
input long h_pid float(year age c_age1 c_age2 c_age3)
22502 2005 26  .  .  .
22502 2006 27  .  .  .
22502 2007 28  .  .  .
22502 2008 29  1  .  .
22502 2009 30  2  .  .
22502 2010 31  3  .  .
22502 2011 32  4  .  .
22502 2012 33  5  .  .
22502 2013 34  6  .  .
22502 2014 35  7  .  .
22502 2015 36  8  .  .
22502 2016 37  9  .  .
22502 2017 38 10  .  .
22502 2018 39 11  .  .
22502 2019 40 12  .  .
22502 2020 41 13  .  .
23101 2005 53  .  .  .
23101 2006 54  .  .  .
23101 2007 55  .  .  .
23101 2008 56  .  .  .
23101 2009 57  .  .  .
23101 2010 58  .  .  .
23101 2011 59  .  .  .
23101 2012 60  .  .  .
23101 2013 61  .  .  .
23101 2014 62  .  .  .
23101 2015 63  .  .  .
23101 2016 64  .  .  .
23101 2017 65  .  .  .
23101 2018 66  .  .  .
23101 2019 67  .  .  .
23101 2020 68  .  .  .
23102 2005 49  .  .  .
23102 2006 50  .  .  .
23102 2007 51  .  .  .
23102 2008 52  .  .  .
23102 2009 53  .  .  .
23102 2010 54  .  .  .
23102 2011 55  .  .  .
23102 2012 56  .  .  .
23102 2013 57  .  .  .
23102 2014 58  .  .  .
23102 2015 59  .  .  .
23102 2016 60  .  .  .
23102 2017 61  .  .  .
23102 2018 62  .  .  .
23102 2019 63  .  .  .
23102 2020 64  .  .  .
23103 2005 21  .  .  .
23103 2006 22  .  .  .
23103 2007 23  .  .  .
23103 2008 24  .  .  .
23103 2009 25  .  .  .
23103 2010 26  .  .  .
23103 2011 27  1  .  .
23103 2012 28  2  .  .
23103 2013 29  3  .  .
23103 2014 30  4  1  .
23103 2015 31  5  2  .
23103 2016 32  6  3  .
23103 2017 33  7  4  .
23103 2018 34  8  5  .
23103 2019 35  9  6  .
23151 2009 26  .  .  .
23151 2010 27  .  .  .
23151 2011 28  .  .  .
23151 2012 29  .  .  .
23151 2013 30  .  .  .
23151 2014 31  .  .  .
23151 2015 32  .  .  .
23151 2016 33  .  .  .
23151 2017 34  .  .  .
23151 2018 35  .  .  .
23151 2019 36  .  .  .
23601 2005 53  .  .  .
23601 2006 54  .  .  .
23601 2007 55  .  .  .
23601 2008 56  .  .  .
23601 2011 59  .  .  .
23601 2012 60  .  .  .
23602 2005 53 11  .  .
23602 2006 54 12  .  .
23602 2007 55 13  .  .
23602 2008 56 14  .  .
23602 2011 59 17  .  .
23602 2012 60 18  .  .
23603 2005 26  .  .  .
23603 2006 27  .  .  .
23603 2007 28  .  .  .
end

[William Lisowski]

This example assumes your data always contains consecutive years within each h_pid - no gaps.

generate nyear = -year
bysort h_pid (nyear): replace c_age1 = c_age1[_n-1]-1 if missing(c_age1)
drop nyear
sort h_pid year
list, sepby(h_pid)

. list, sepby(h_pid)

     +-----------------------------+
     | h_pid   year   age   c_age1 |
     |-----------------------------|
  1. | 22502   2005    26       -2 |
  2. | 22502   2006    27       -1 |
  3. | 22502   2007    28        0 |
  4. | 22502   2008    29        1 |
  5. | 22502   2009    30        2 |
  6. | 22502   2010    31        3 |
  7. | 22502   2011    32        4 |
  8. | 22502   2012    33        5 |
  9. | 22502   2013    34        6 |
 10. | 22502   2014    35        7 |
 11. | 22502   2015    36        8 |
 12. | 22502   2016    37        9 |
 13. | 22502   2017    38       10 |
 14. | 22502   2018    39       11 |
 15. | 22502   2019    40       12 |
 16. | 22502   2020    41       13 |
     |-----------------------------|
 17. | 23101   2005    53        . |
 18. | 23101   2006    54        . |
 19. | 23101   2007    55        . |
 20. | 23101   2008    56        . |
 21. | 23101   2009    57        . |
 22. | 23101   2010    58        . |
 23. | 23101   2011    59        . |
 24. | 23101   2012    60        . |
 25. | 23101   2013    61        . |
 26. | 23101   2014    62        . |
 27. | 23101   2015    63        . |
 28. | 23101   2016    64        . |
 29. | 23101   2017    65        . |
 30. | 23101   2018    66        . |
 31. | 23101   2019    67        . |
 32. | 23101   2020    68        . |
     |-----------------------------|
 33. | 23102   2005    49        . |
 34. | 23102   2006    50        . |
 35. | 23102   2007    51        . |
 36. | 23102   2008    52        . |
 37. | 23102   2009    53        . |
 38. | 23102   2010    54        . |
 39. | 23102   2011    55        . |
 40. | 23102   2012    56        . |
 41. | 23102   2013    57        . |
 42. | 23102   2014    58        . |
 43. | 23102   2015    59        . |
 44. | 23102   2016    60        . |
 45. | 23102   2017    61        . |
 46. | 23102   2018    62        . |
 47. | 23102   2019    63        . |
 48. | 23102   2020    64        . |
     |-----------------------------|
 49. | 23103   2005    21       -5 |
 50. | 23103   2006    22       -4 |
 51. | 23103   2007    23       -3 |
 52. | 23103   2008    24       -2 |
 53. | 23103   2009    25       -1 |
 54. | 23103   2010    26        0 |
 55. | 23103   2011    27        1 |
 56. | 23103   2012    28        2 |
 57. | 23103   2013    29        3 |
 58. | 23103   2014    30        4 |
 59. | 23103   2015    31        5 |
 60. | 23103   2016    32        6 |
 61. | 23103   2017    33        7 |
 62. | 23103   2018    34        8 |
 63. | 23103   2019    35        9 |
     |-----------------------------|
 64. | 23151   2009    26        . |
 65. | 23151   2010    27        . |
 66. | 23151   2011    28        . |
 67. | 23151   2012    29        . |
 68. | 23151   2013    30        . |
 69. | 23151   2014    31        . |
 70. | 23151   2015    32        . |
 71. | 23151   2016    33        . |
 72. | 23151   2017    34        . |
 73. | 23151   2018    35        . |
 74. | 23151   2019    36        . |
     |-----------------------------|
 75. | 23601   2005    53        . |
 76. | 23601   2006    54        . |
 77. | 23601   2007    55        . |
 78. | 23601   2008    56        . |
 79. | 23601   2011    59        . |
 80. | 23601   2012    60        . |
     |-----------------------------|
 81. | 23602   2005    53       11 |
 82. | 23602   2006    54       12 |
 83. | 23602   2007    55       13 |
 84. | 23602   2008    56       14 |
 85. | 23602   2011    59       17 |
 86. | 23602   2012    60       18 |
     |-----------------------------|
 87. | 23603   2005    26        . |
 88. | 23603   2006    27        . |
 89. | 23603   2007    28        . |
     +-----------------------------+

[Øyvind Snilsberg]

bysort h_pid (c_age1): gen wanted = 1 + age - cond(c_age1[1]<., age[1], .)