Generating the Sum over rolling Time Periods

[Max Weir]

Hello!

I would like to generate a new variable that shows the sum of months of unemployment (1-12) for each three year time period (1984-1986, 1985-1987 etc) throughout the time period 1984 to 2020 for each person/observation. The variable should show the number of months a person is unemployed for each three year time period. For example if a person x was unemployed for 2 months in 1984, 4 months in 1985 and 5 months in 1986 their total of unemployment for the time period 1984-1986 would be 11 months. Consequently, if person x was unemployed for 0 months in 1987 their total of unemployed months in the three year period of 1985-1987 would fall to 9 months.

* Example generated by -dataex-. For more info, type help dataex
clear
input double syear_01 long pid_01 byte kal1d02_01
1984 101  0
1985 101  0
1986 101  0
1987 101  0
1988 101  0
1989 101  0
1984 102  0
1985 102  0
1986 102  5
1987 102  0
1988 102  0
1989 102  0
1984 103  0
1985 103  0
1986 103  0
1987 103  0
1984 201  0
1985 201  0
1986 201  0
1987 201  0
1988 201  0
1989 201  0
1990 201  0
1991 201  0
1992 201  0
1993 201  6
1994 201  6
1995 201  0
1996 201  0
1997 201  0
1998 201  0
1999 201  0
2000 201  0
2001 201  0
2002 201  0
2003 201  0
2004 201  0
2005 201  0
2006 201  0
2007 201  0
2008 201  0
1985 202  0
1986 202  0
1987 202  0
1985 203  0
1986 203  0
1987 203  0
1988 203  0
1989 203  0
1990 203  0
1991 203  0
1992 203  0
1993 203  2
1994 203 12
1995 203  0
1996 203  0
1997 203  3
1998 203  3
1999 203  3
2000 203  0
2001 203  0
2002 203  0
2003 203  0
2004 203  0
2005 203  0
2006 203  0
2007 203  0
2008 203  0
1984 301  0
1985 301  0
1986 301  0
1987 301  0
1988 301  0
1989 301  0
1990 301  0
1991 301  0
1992 301  0
1993 301  0
1984 302  0
1985 302  0
1986 302  0
1987 302  0
1988 302  3
1989 302  0
1990 302  0
1991 302  0
1992 302  0
1993 302  0
1984 401  0
1985 401  0
1986 401  0
1984 501  0
1985 501  0
1986 501  8
1987 501  0
1988 501  0
1989 501  0
1990 501  0
1991 501  0
1992 501  0
end

syear_01: survey year, only taking values between 1984 and 2020
pid_01: personal identification number
kal1d02_01: shows the months of unemployment a person experienced in the previous year.

eg. the new variable should show that pid 102 would have a value of 5 months of unemployment in the time period 1986-1988 and have a value of 0 months of unemployment in the time period 1987-1989.

I have tried with this command:

bys pid_01 (syear_01): egen total = total( kal1d02_01 ) if inrange(syear_01, 1984, 2020)
bys pid_01: egen wanted= max(total)
drop total
bys pid_01 (syear_01): replace wanted= 0 if _n!=_N

But this just summates over the whole period from 1984 and 2020, rather than the three year periods I am aiming to make.

Does anyone know how to create a variable such as this?

Thank you in advance.

Best Wishes,
Max

[Daniel Schaefer]

Hi Max, I may be misunderstanding something, but you want to know the total number of unemployed months for each person for each 3 year period, correct? Doesn't that mean that you need a separate variable for each 3 year period? For example, how can a single new variable show that pid 102 has a value of 5 months of unemployment in 1986-1988 and 0 months of unemployment in the time period 1987-1989? Isn't there only a single value within your variable associated with pid 102?

[Nick Cox]

rangestat from SSC offers one solution here.

You must install it before you can use it with

ssc install rangestat

clear
input double syear_01 long pid_01 byte kal1d02_01
1984 101  0
1985 101  0
1986 101  0
1987 101  0
1988 101  0
1989 101  0
1984 102  0
1985 102  0
1986 102  5
1987 102  0
1988 102  0
1989 102  0
1984 103  0
1985 103  0
1986 103  0
1987 103  0
1984 201  0
1985 201  0
1986 201  0
1987 201  0
1988 201  0
1989 201  0
1990 201  0
1991 201  0
1992 201  0
1993 201  6
1994 201  6
1995 201  0
1996 201  0
1997 201  0
1998 201  0
1999 201  0
2000 201  0
2001 201  0
2002 201  0
2003 201  0
2004 201  0
2005 201  0
2006 201  0
2007 201  0
2008 201  0
1985 202  0
1986 202  0
1987 202  0
1985 203  0
1986 203  0
1987 203  0
1988 203  0
1989 203  0
1990 203  0
1991 203  0
1992 203  0
1993 203  2
1994 203 12
1995 203  0
1996 203  0
1997 203  3
1998 203  3
1999 203  3
2000 203  0
2001 203  0
2002 203  0
2003 203  0
2004 203  0
2005 203  0
2006 203  0
2007 203  0
2008 203  0
1984 301  0
1985 301  0
1986 301  0
1987 301  0
1988 301  0
1989 301  0
1990 301  0
1991 301  0
1992 301  0
1993 301  0
1984 302  0
1985 302  0
1986 302  0
1987 302  0
1988 302  3
1989 302  0
1990 302  0
1991 302  0
1992 302  0
1993 302  0
1984 401  0
1985 401  0
1986 401  0
1984 501  0
1985 501  0
1986 501  8
1987 501  0
1988 501  0
1989 501  0
1990 501  0
1991 501  0
1992 501  0
end

rangestat (sum) kal1d02_01 (count) kal1d02_01, int(syear_01 -2  0) by(pid_01)

list, sepby(pid_01)

    +----------------------------------------------------+
     | syear_01   pid_01   kal1d~01   kal1d0~m   kal1d0~t |
     |----------------------------------------------------|
  1. |     1984      101          0          0          1 |
  2. |     1985      101          0          0          2 |
  3. |     1986      101          0          0          3 |
  4. |     1987      101          0          0          3 |
  5. |     1988      101          0          0          3 |
  6. |     1989      101          0          0          3 |
     |----------------------------------------------------|
  7. |     1984      102          0          0          1 |
  8. |     1985      102          0          0          2 |
  9. |     1986      102          5          5          3 |
 10. |     1987      102          0          5          3 |
 11. |     1988      102          0          5          3 |
 12. |     1989      102          0          0          3 |
     |----------------------------------------------------|
 13. |     1984      103          0          0          1 |
 14. |     1985      103          0          0          2 |
 15. |     1986      103          0          0          3 |
 16. |     1987      103          0          0          3 |
     |----------------------------------------------------|
 17. |     1984      201          0          0          1 |
 18. |     1985      201          0          0          2 |
 19. |     1986      201          0          0          3 |
 20. |     1987      201          0          0          3 |
 21. |     1988      201          0          0          3 |
 22. |     1989      201          0          0          3 |
 23. |     1990      201          0          0          3 |
 24. |     1991      201          0          0          3 |
 25. |     1992      201          0          0          3 |
 26. |     1993      201          6          6          3 |
 27. |     1994      201          6         12          3 |
 28. |     1995      201          0         12          3 |
 29. |     1996      201          0          6          3 |
 30. |     1997      201          0          0          3 |
 31. |     1998      201          0          0          3 |
 32. |     1999      201          0          0          3 |
 33. |     2000      201          0          0          3 |
 34. |     2001      201          0          0          3 |
 35. |     2002      201          0          0          3 |
 36. |     2003      201          0          0          3 |
 37. |     2004      201          0          0          3 |
 38. |     2005      201          0          0          3 |
 39. |     2006      201          0          0          3 |
 40. |     2007      201          0          0          3 |
 41. |     2008      201          0          0          3 |
     |----------------------------------------------------|
 42. |     1985      202          0          0          1 |
 43. |     1986      202          0          0          2 |
 44. |     1987      202          0          0          3 |
     |----------------------------------------------------|
 45. |     1985      203          0          0          1 |
 46. |     1986      203          0          0          2 |
 47. |     1987      203          0          0          3 |
 48. |     1988      203          0          0          3 |
 49. |     1989      203          0          0          3 |
 50. |     1990      203          0          0          3 |
 51. |     1991      203          0          0          3 |
 52. |     1992      203          0          0          3 |
 53. |     1993      203          2          2          3 |
 54. |     1994      203         12         14          3 |
 55. |     1995      203          0         14          3 |
 56. |     1996      203          0         12          3 |
 57. |     1997      203          3          3          3 |
 58. |     1998      203          3          6          3 |
 59. |     1999      203          3          9          3 |
 60. |     2000      203          0          6          3 |
 61. |     2001      203          0          3          3 |
 62. |     2002      203          0          0          3 |
 63. |     2003      203          0          0          3 |
 64. |     2004      203          0          0          3 |
 65. |     2005      203          0          0          3 |
 66. |     2006      203          0          0          3 |
 67. |     2007      203          0          0          3 |
 68. |     2008      203          0          0          3 |
     |----------------------------------------------------|
 69. |     1984      301          0          0          1 |
 70. |     1985      301          0          0          2 |
 71. |     1986      301          0          0          3 |
 72. |     1987      301          0          0          3 |
 73. |     1988      301          0          0          3 |
 74. |     1989      301          0          0          3 |
 75. |     1990      301          0          0          3 |
 76. |     1991      301          0          0          3 |
 77. |     1992      301          0          0          3 |
 78. |     1993      301          0          0          3 |
     |----------------------------------------------------|
 79. |     1984      302          0          0          1 |
 80. |     1985      302          0          0          2 |
 81. |     1986      302          0          0          3 |
 82. |     1987      302          0          0          3 |
 83. |     1988      302          3          3          3 |
 84. |     1989      302          0          3          3 |
 85. |     1990      302          0          3          3 |
 86. |     1991      302          0          0          3 |
 87. |     1992      302          0          0          3 |
 88. |     1993      302          0          0          3 |
     |----------------------------------------------------|
 89. |     1984      401          0          0          1 |
 90. |     1985      401          0          0          2 |
 91. |     1986      401          0          0          3 |
     |----------------------------------------------------|
 92. |     1984      501          0          0          1 |
 93. |     1985      501          0          0          2 |
 94. |     1986      501          8          8          3 |
 95. |     1987      501          0          8          3 |
 96. |     1988      501          0          8          3 |
 97. |     1989      501          0          0          3 |
 98. |     1990      501          0          0          3 |
 99. |     1991      501          0          0          3 |
100. |     1992      501          0          0          3 |
     +----------------------------------------------------+

This code places new values in the last year of each 3-year period, and clearly you have scope to use the first or middle year instead, or even any other year.


Note that with official Stata and say

xtset pid_01 syear_01 

gen wanted = kal1d02_01 + L1.kal1d02_01 + L2.kal1d02_01

you will get missing values in the results if any of the arguments on the right-hand side do not exist or are missing. The rangestat code counts non-missing values so that you can watch out for gaps or incomplete trios at the beginning of each panel. You could modify the code to get the same result as rangestat.

[Max Weir]

Hi Nick,
Thank you for your help it is really useful and has given me the data i wanted thank you.

Best Wishes,
Max