How to calculate ten-year moving average of variable x by the dummy variable europe in a dataset as the following? Many thanks!

[Linghui Han]

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id_number int year float europe double x
  1 1302 1 1.1393939393939394
  2 1302 1                 .4
  3 1304 1                  .
  4 1304 1 1.0714285714285714
  5 1306 1  .6666666666666666
  6 1307 1                 .2
  7 1311 1  .4166666666666667
  8 1313 1                  .
  9 1314 1  .3865979381443299
 10 1315 1                  .
 11 1315 1              .1875
 12 1316 1 1.4285714285714286
 13 1316 1                  .
 14 1316 1                  .
 15 1318 1                  .
 16 1318 1                  1
 17 1319 1                  .
 18 1322 1                  .
 19 1322 1                  0
 20 1322 1                  4
 21 1322 1 1.2857142857142858
 22 1325 1             .21875
 23 1325 1                  .
 24 1328 1                  1
 25 1329 1                  2
 26 1329 1                  .
 27 1330 1  .3333333333333333
 28 1330 1                  .
 29 1332 1               1.78
 30 1332 1  .7142857142857143
 31 1333 1  .6923076923076923
 32 1336 0                  2
 33 1337 1                  .
 34 1337 0                  .
 35 1340 1                  .
 36 1340 1                .24
 37 1340 1                  .
 38 1341 1                  .
 39 1342 1                  .
 40 1344 1  2.066666666666667
 41 1345 1 .21428571428571427
 42 1345 1                  .
 43 1345 1 3.7735849056603774
 44 1345 1                  .
 45 1345 1                  .
 46 1346 1                  .
 47 1346 1                .18
 48 1346 1                  8
 49 1346 1 1.4285714285714286
 50 1346 1                 .3
 51 1346 1  .5416666666666666
 52 1347 1                  0
 53 1347 1                  .
 54 1347 1                  0
 55 1349 1                  3
 56 1349 1                  .
 57 1350 1                  .
 58 1351 1                  1
 59 1351 1                  .
 60 1352 1                 .4
 61 1354 1                  .
 62 1356 1  .2222222222222222
 63 1356 1  .5454545454545454
 64 1358 1  .4444444444444444
 65 1361 1                  .
 66 1362 1                  .
 67 1362 1                  .
 68 1364 1 .40454545454545454
 69 1364 1                  3
 70 1364 1               .875
 71 1365 1                  .
 72 1367 1  .4666666666666667
 73 1369 1                  .
 74 1370 1                 .7
 75 1371 1                  .
 76 1371 1                .55
 77 1372 1                  .
 78 1373 1                  .
 79 1377 1                  .
 80 1377 1                  .
 81 1377 1                  .
 82 1380 1                4.4
 83 1381 1                  .
 84 1382 1 1.1428571428571428
 85 1384 1                .28
 86 1385 1                  .
 87 1385 1                 .5
 88 1385 1 .20967741935483872
 89 1385 0                .15
 90 1385 1                  .
 91 1386 1                .35
 92 1386 1 1.6666666666666667
 93 1387 1  .6113207547169811
 94 1388 1             .90625
 95 1388 1  .3888888888888889
 96 1389 1 1.5952380952380953
 97 1391 1                  .
 98 1394 1                  .
 99 1394 1                  .
100 1395 1                  .
end

[Clyde Schechter]

Your question leaves out a lot of detail that is needed for a complete solution. But the gist of it is:
[coe]
rangestat (mean) x, by(europe) interval(year -9 0)
[/code]
This assumes that your ten year moving average runs from 9 year ago through the current year. If you meant 10 years ago through the past year, change the interval option to -interval(year -10 -1)-. If you meant it to run from 5 year ago through 4 years from now, -interval(year -4 5)-. Also you have a lot of missing value observations. Often people only want to calculate the moving average if all 10 years are present or if at least some other minimum number of years can be included. If that's your situation, at (count) x before (mean) x and then you can add a command replacing x_mean by missing value if x_count is less than the minimum number of years you require.

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer; it is available from SSC

[Linghui Han]

Thank you very much! Yes, I do want to calculate the moving average with all ten years are present (not counting those years with missing values). But with the following code:

rangestat (mean) x, by(europe) interval(year 0 9)

the mean I got is the mean for ten consecutive years instead of ten years in my sample. Note that the year variable in my sample has missing years. Also I don't want the calculation including those observations with missing x.

[Linghui Han]

Correction: Using the Code in #3, the mean I got does not equal to the mean of the ten years from year 0 to year 9 which are not consecutive years. I am wondering why.

[Nick Cox]

Your years are irregular and often repeated. So rangestat which is averaging over [year, year + 9] could be averaging just however many observations are in that interval, which need not be 10.

[Nick Cox]

Your years are irregular and often repeated. So rangestat which is averaging over [year, year + 9] will be averaging over however many observations with non-missing values are in that interval, which need not be 10.

Missing values are not included in the calculation. I don’t know how that would even be possible, but the command won’t try to include missing values in the mean.

[Linghui Han]

Many thanks! So for one example, the calculation done by this code means that it averages over the observations in year 1302, 1304, 1306, 1307, 1311, 1313, 1314, 1315, 1316, 1318. Is this understanding correct?

[Linghui Han]

So for one example, the calculation done by this code means that it averages over the observations in year 1302, 1304, 1306, 1307, 1311, 1313, 1314, 1315, 1316, 1318? From the mean of x I got, it seems not correct:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id_number int year double(x x_mean_ma)
  1 1302 1.1393939393939394  .6490259740259741
  2 1302                 .4  .6490259740259741
  3 1304                  .  .5886904761904762
  4 1304 1.0714285714285714  .5886904761904762
  5 1306  .6666666666666666 .37148625429553267
  6 1307                 .2  .5238672066764851
  7 1311  .4166666666666667   .683867206676485
  8 1313                  . 1.1840548074900064
  9 1314  .3865979381443299 1.1840548074900064
 10 1315                  . 1.3169642857142858
 11 1315              .1875 1.3169642857142858
 12 1316 1.4285714285714286  1.322172619047619
 13 1316                  .  1.322172619047619
 14 1316                  .  1.322172619047619
 15 1318                  .  1.300892857142857
 16 1318                  1  1.300892857142857
 17 1319                  .  1.300892857142857
 18 1322                  . 1.2625425170068028
 19 1322                  0 1.2625425170068028
 20 1322                  4 1.2625425170068028
 21 1322 1.2857142857142858 1.2625425170068028
 22 1325             .21875    .96266810570382
 23 1325                  .    .96266810570382
 24 1328                  1 1.0866544566544567
 25 1329                  2  1.103985347985348
 26 1329                  .  1.103985347985348
 27 1330  .3333333333333333  .8799816849816849
 28 1330                  .  .8799816849816849
 29 1332               1.78  .8566483516483516
 30 1332  .7142857142857143  .8566483516483516
 31 1333  .6923076923076923 .46615384615384614
 32 1336                  2                  2
 33 1337                  . 1.8605305979834281
 34 1337                  .                  .
 35 1340                  . 1.6453979484875711
 36 1340                .24 1.6453979484875711
 37 1340                  . 1.6453979484875711
 38 1341                  . 1.7731613983500778
 39 1342                  . 1.7087312818209046
 40 1344  2.066666666666667  1.608059644757758
 41 1345 .21428571428571427 1.5698423929320156
 42 1345                  . 1.5698423929320156
 43 1345 3.7735849056603774 1.5698423929320156
 44 1345                  . 1.5698423929320156
 45 1345                  . 1.5698423929320156
 46 1346                  . 1.4850238095238095
 47 1346                .18 1.4850238095238095
 48 1346                  8 1.4850238095238095
 49 1346 1.4285714285714286 1.4850238095238095
 50 1346                 .3 1.4850238095238095
 51 1346  .5416666666666666 1.4850238095238095
 52 1347                  0  .7382395382395383
 53 1347                  .  .7382395382395383
 54 1347                  0  .7382395382395383
 55 1349                  3  .9353535353535354
 56 1349                  .  .9353535353535354
 57 1350                  .  .5224242424242425
 58 1351                  1  .5224242424242425
 59 1351                  .  .5224242424242425
 60 1352                 .4   .403030303030303
 61 1354                  .   .404040404040404
 62 1356  .2222222222222222  .9152777777777777
 63 1356  .5454545454545454  .9152777777777777
 64 1358  .4444444444444444 1.0381313131313132
 65 1361                  . 1.0892424242424243
 66 1362                  .  .9993686868686869
 67 1362                  .  .9993686868686869
 68 1364 .40454545454545454  .9993686868686869
 69 1364                  3  .9993686868686869
 70 1364               .875  .9993686868686869
 71 1365                  .  .5722222222222223
 72 1367  .4666666666666667  .5722222222222223
 73 1369                  .               .625
 74 1370                 .7               .625
 75 1371                  .              2.475
 76 1371                .55              2.475
 77 1372                  .                4.4
 78 1373                  . 2.7714285714285714
 79 1377                  . 1.2213144612683784
 80 1377                  . 1.2213144612683784
 81 1377                  . 1.2213144612683784
 82 1380                4.4  1.095536269792965
 83 1381                  .  .7650898967722614
 84 1382 1.1428571428571428  .7650898967722614
 85 1384                .28  .7231157583183856
 86 1385                  .  .7785052281081839
 87 1385                 .5  .7785052281081839
 88 1385 .20967741935483872  .7785052281081839
 89 1385                .15                .15
 90 1385                  .  .7785052281081839
 91 1386                .35  .9197274009184387
 92 1386 1.6666666666666667  .9197274009184387
 93 1387  .6113207547169811    .91099724218828
 94 1388             .90625  .9709325396825397
 95 1388  .3888888888888889  .9709325396825397
 96 1389 1.5952380952380953 1.1865079365079365
 97 1391                  .  1.444235588972431
 98 1394                  .  .7656711222500696
 99 1394                  .  .7656711222500696
100 1395                  .  .7656711222500696
end

[Nick Cox]

No, because (for example) if the year is 1302 then at most the interval is 1302 + 0 to 1302 + 9 or 1302 to 1311. No window of length 10 includes all those years. The point can be made with just some of your data example.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id_number int year float europe double x
  1 1302 1 1.1393939393939394
  2 1302 1                 .4
  3 1304 1                  .
  4 1304 1 1.0714285714285714
  5 1306 1  .6666666666666666
  6 1307 1                 .2
  7 1311 1  .4166666666666667
end

rangestat x, int(year 0 9) by(europe) 

list in 1 

     +--------------------------------------------------+
     | id_num~r   year   europe           x      x_mean |
     |--------------------------------------------------|
  1. |        1   1302        1   1.1393939   .64902597 |
     +--------------------------------------------------+

. 
. su x if inrange(year, 1302 + 0, 1302 + 9) 

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
           x |          6     .649026    .3838815         .2   1.139394

For the first observation the rangestat window includes 6 observations with non-missing values. Independently it's clear that summarize shows the same mean.

[Linghui Han]

I see. But how to average over the observations in year 1302, 1304, 1306, 1307, 1311, 1313, 1314, 1315, 1316, 1318 which span ten sample years?

[Nick Cox]

Well, that is not a ten-year moving average in the sense of any literature I have read in the last 50 years. But if (and only if) it makes sense to you to count years observed and ignore gaps, then you need a different pseudo-time metric, perhaps like this:

bysort europe (year) : gen newyear = sum(year != year[_n-1]) 

list, sepby(year)


    +------------------------------------------------+
     | id_num~r   year   europe           x   newyear |
     |------------------------------------------------|
  1. |       32   1336        0           2         1 |
     |------------------------------------------------|
  2. |       34   1337        0           .         2 |
     |------------------------------------------------|
  3. |       89   1385        0         .15         3 |
     |------------------------------------------------|
  4. |        1   1302        1   1.1393939         1 |
  5. |        2   1302        1          .4         1 |
     |------------------------------------------------|
  6. |        3   1304        1           .         2 |
  7. |        4   1304        1   1.0714286         2 |
     |------------------------------------------------|
  8. |        5   1306        1   .66666667         3 |
     |------------------------------------------------|
  9. |        6   1307        1          .2         4 |
     |------------------------------------------------|
 10. |        7   1311        1   .41666667         5 |
     |------------------------------------------------|
 11. |        8   1313        1           .         6 |
     |------------------------------------------------|
 12. |        9   1314        1   .38659794         7 |
     |------------------------------------------------|
 13. |       11   1315        1       .1875         8 |
 14. |       10   1315        1           .         8 |
     |------------------------------------------------|
 15. |       13   1316        1           .         9 |
 16. |       14   1316        1           .         9 |
 17. |       12   1316        1   1.4285714         9 |
     |------------------------------------------------|
 18. |       15   1318        1           .        10 |
 19. |       16   1318        1           1        10 |
     |------------------------------------------------|
 20. |       17   1319        1           .        11 |
     |------------------------------------------------|
 21. |       18   1322        1           .        12 |
 22. |       21   1322        1   1.2857143        12 |
 23. |       20   1322        1           4        12 |
 24. |       19   1322        1           0        12 |
     |------------------------------------------------|
 25. |       23   1325        1           .        13 |
 26. |       22   1325        1      .21875        13 |
     |------------------------------------------------|
 27. |       24   1328        1           1        14 |
     |------------------------------------------------|
 28. |       25   1329        1           2        15 |
 29. |       26   1329        1           .        15 |
     |------------------------------------------------|
 30. |       27   1330        1   .33333333        16 |
 31. |       28   1330        1           .        16 |
     |------------------------------------------------|
 32. |       29   1332        1        1.78        17 |
 33. |       30   1332        1   .71428571        17 |
     |------------------------------------------------|
 34. |       31   1333        1   .69230769        18 |
     |------------------------------------------------|
 35. |       33   1337        1           .        19 |
     |------------------------------------------------|
 36. |       36   1340        1         .24        20 |
 37. |       37   1340        1           .        20 |
 38. |       35   1340        1           .        20 |
     |------------------------------------------------|
 39. |       38   1341        1           .        21 |
     |------------------------------------------------|
 40. |       39   1342        1           .        22 |
     |------------------------------------------------|
 41. |       40   1344        1   2.0666667        23 |
     |------------------------------------------------|
 42. |       41   1345        1   .21428571        24 |
 43. |       45   1345        1           .        24 |
 44. |       42   1345        1           .        24 |
 45. |       43   1345        1   3.7735849        24 |
 46. |       44   1345        1           .        24 |
     |------------------------------------------------|
 47. |       51   1346        1   .54166667        25 |
 48. |       50   1346        1          .3        25 |
 49. |       46   1346        1           .        25 |
 50. |       48   1346        1           8        25 |
 51. |       47   1346        1         .18        25 |
 52. |       49   1346        1   1.4285714        25 |
     |------------------------------------------------|
 53. |       52   1347        1           0        26 |
 54. |       54   1347        1           0        26 |
 55. |       53   1347        1           .        26 |
     |------------------------------------------------|
 56. |       56   1349        1           .        27 |
 57. |       55   1349        1           3        27 |
     |------------------------------------------------|
 58. |       57   1350        1           .        28 |
     |------------------------------------------------|
 59. |       58   1351        1           1        29 |
 60. |       59   1351        1           .        29 |
     |------------------------------------------------|
 61. |       60   1352        1          .4        30 |
     |------------------------------------------------|
 62. |       61   1354        1           .        31 |
     |------------------------------------------------|
 63. |       63   1356        1   .54545455        32 |
 64. |       62   1356        1   .22222222        32 |
     |------------------------------------------------|
 65. |       64   1358        1   .44444444        33 |
     |------------------------------------------------|
 66. |       65   1361        1           .        34 |
     |------------------------------------------------|
 67. |       67   1362        1           .        35 |
 68. |       66   1362        1           .        35 |
     |------------------------------------------------|
 69. |       70   1364        1        .875        36 |
 70. |       69   1364        1           3        36 |
 71. |       68   1364        1   .40454545        36 |
     |------------------------------------------------|
 72. |       71   1365        1           .        37 |
     |------------------------------------------------|
 73. |       72   1367        1   .46666667        38 |
     |------------------------------------------------|
 74. |       73   1369        1           .        39 |
     |------------------------------------------------|
 75. |       74   1370        1          .7        40 |
     |------------------------------------------------|
 76. |       76   1371        1         .55        41 |
 77. |       75   1371        1           .        41 |
     |------------------------------------------------|
 78. |       77   1372        1           .        42 |
     |------------------------------------------------|
 79. |       78   1373        1           .        43 |
     |------------------------------------------------|
 80. |       79   1377        1           .        44 |
 81. |       81   1377        1           .        44 |
 82. |       80   1377        1           .        44 |
     |------------------------------------------------|
 83. |       82   1380        1         4.4        45 |
     |------------------------------------------------|
 84. |       83   1381        1           .        46 |
     |------------------------------------------------|
 85. |       84   1382        1   1.1428571        47 |
     |------------------------------------------------|
 86. |       85   1384        1         .28        48 |
     |------------------------------------------------|
 87. |       86   1385        1           .        49 |
 88. |       87   1385        1          .5        49 |
 89. |       88   1385        1   .20967742        49 |
 90. |       90   1385        1           .        49 |
     |------------------------------------------------|
 91. |       91   1386        1         .35        50 |
 92. |       92   1386        1   1.6666667        50 |
     |------------------------------------------------|
 93. |       93   1387        1   .61132075        51 |
     |------------------------------------------------|
 94. |       94   1388        1      .90625        52 |
 95. |       95   1388        1   .38888889        52 |
     |------------------------------------------------|
 96. |       96   1389        1   1.5952381        53 |
     |------------------------------------------------|
 97. |       97   1391        1           .        54 |
     |------------------------------------------------|
 98. |       99   1394        1           .        55 |
 99. |       98   1394        1           .        55 |
     |------------------------------------------------|
100. |      100   1395        1           .        56 |
     +------------------------------------------------+

.

[Linghui Han]

Actually, if using tssmooth by designating leads and lags, the ma will be average over sample years. Is this understanding correct?

[Linghui Han]

Actually, if using tssmooth by designating leads and lags, the ma will be average over sample years with gaps instead of natural years. If using -rangestat (mean) x, by(europe) interval(year 0 9)-, it is average over consecutive natural years. Is this understanding correct?

[Nick Cox]

You don't appear to be new to Stata or Statalist, so I am puzzled by your questions.

Why not just try out your ideas with simple datasets?

But FWIW tssmooth will not behave as you wish without creating a pseudo-time variable as explained.

[Nick Cox]

Correction: tssmooth would not work on your data, regardless. tssmooth requires a prior tsset or xtset and either would fail with your data given repeated time values.