Taking yearly average in panel data.

[Demet Korkut]

Hello,

I am working on a balanced panel data set whose example is shown below. I want to take yearly average of the variables A and B (seperately) and for each nace2. But my aim is to take average of the last four years, for example for the nace2 variable '51' and year '2013': the average for A should only include the years 2010-2011-2012 and 2013. Moreover, should I handle with missing values seperately? I tried this code but it did not work:

foreach var of varlist A B {
by nace2: egen `var'_avg=mean(`var') if inrange(year,year[_n-4],year[_n])
}

Thank you for your contributions.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(year nace2) float(A P)
2009 51 1461.2297  .010234712
2010 51 2060.3274  .011217712
2011 51 1631.6633  .009138515
2012 51         .  .009079802
2013 51  219.1862  .014016564
2014 51 273.92587   .01216662
2015 51  270.4855  .014872827
2009 52  2700.143  .013527314
2010 52  8844.973  .013993295
2011 52   15835.6  .014242434
2012 52  9403.576   .01332812
2013 52  12200.78  .012611322
2014 52  8156.921  .015147976
2015 52  5486.392  .013481406
2009 61  14059968  .012168975
2010 61   9527888  .006494127
2011 61  12027033  .003669558
2012 61   5539147  .003874143
2013 61   2201740 .0041708336
2014 61 4383958.5  .005500489
2015 61  18545630  .009940005
2009 62   3454367  .017328385
2010 62 2562616.5  .016870743
2011 62   6055793  .011037342
2012 62  946574.3           .
2013 62 189954.63           .
2014 62  423862.8           .
2015 62         0           .
2009 71         0  .007502351
2010 71         0  .010514058
2011 71         0  .006451322
2012 71  393.2239  .006296175
2013 71 12818.802  .007285675
2014 71 21111.506  .011580366
2015 71 33907.652  .009038523
end

[Nick Cox]

The help for egen carries a warning

Explicit subscripting (using _N and _n), which is commonly used with
generate, should not be used with egen; see subscripting.

and in any case egen offers no support for rolling calculations. Had it worked, your syntax would have given you means of 5 observations as _n-4 _n-3 _n-2 _n-1 _n together index windows of 5.

rangestat (SSC) may help. Note that this example is minimal; you may calculate other summaries at the same time, such as counts of non-missing values.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(year nace2) float(A P)
2009 51 1461.2297  .010234712
2010 51 2060.3274  .011217712
2011 51 1631.6633  .009138515
2012 51         .  .009079802
2013 51  219.1862  .014016564
2014 51 273.92587   .01216662
2015 51  270.4855  .014872827
2009 52  2700.143  .013527314
2010 52  8844.973  .013993295
2011 52   15835.6  .014242434
2012 52  9403.576   .01332812
2013 52  12200.78  .012611322
2014 52  8156.921  .015147976
2015 52  5486.392  .013481406
2009 61  14059968  .012168975
2010 61   9527888  .006494127
2011 61  12027033  .003669558
2012 61   5539147  .003874143
2013 61   2201740 .0041708336
2014 61 4383958.5  .005500489
2015 61  18545630  .009940005
2009 62   3454367  .017328385
2010 62 2562616.5  .016870743
2011 62   6055793  .011037342
2012 62  946574.3           .
2013 62 189954.63           .
2014 62  423862.8           .
2015 62         0           .
2009 71         0  .007502351
2010 71         0  .010514058
2011 71         0  .006451322
2012 71  393.2239  .006296175
2013 71 12818.802  .007285675
2014 71 21111.506  .011580366
2015 71 33907.652  .009038523
end

rangestat A P, int(year -3 0) by(nace2) 

list nace2 year A* P*,  sepby(nace2) 

     +------------------------------------------------------------+
     | nace2   year          A      A_mean          P      P_mean |
     |------------------------------------------------------------|
  1. |    51   2009    1461.23   1461.2297   .0102347   .01023471 |
  2. |    51   2010   2060.327   1760.7786   .0112177   .01072621 |
  3. |    51   2011   1631.663   1717.7402   .0091385   .01019698 |
  4. |    51   2012          .   1717.7402   .0090798   .00991769 |
  5. |    51   2013   219.1862   1303.7256   .0140166   .01086315 |
  6. |    51   2014   273.9259   708.25847   .0121666   .01110038 |
  7. |    51   2015   270.4855   254.53253   .0148728   .01253395 |
     |------------------------------------------------------------|
  8. |    52   2009   2700.143   2700.1431   .0135273   .01352731 |
  9. |    52   2010   8844.973   5772.5579   .0139933    .0137603 |
 10. |    52   2011    15835.6   9126.9051   .0142424   .01392101 |
 11. |    52   2012   9403.576   9196.0729   .0133281   .01377279 |
 12. |    52   2013   12200.78   11571.232   .0126113   .01354379 |
 13. |    52   2014   8156.921   11399.219    .015148   .01383246 |
 14. |    52   2015   5486.392   8811.9174   .0134814   .01364221 |
     |------------------------------------------------------------|
 15. |    61   2009   1.41e+07    14059968    .012169   .01216897 |
 16. |    61   2010    9527888    11793928   .0064941   .00933155 |
 17. |    61   2011   1.20e+07    11871630   .0036696   .00744422 |
 18. |    61   2012    5539147    10288509   .0038741    .0065517 |
 19. |    61   2013    2201740     7323952   .0041708   .00455217 |
 20. |    61   2014    4383959   6037969.6   .0055005   .00430376 |
 21. |    61   2015   1.85e+07   7667618.9     .00994   .00587137 |
     |------------------------------------------------------------|
 22. |    62   2009    3454367     3454367   .0173284   .01732839 |
 23. |    62   2010    2562617   3008491.8   .0168707   .01709956 |
 24. |    62   2011    6055793   4024258.8   .0110373   .01507882 |
 25. |    62   2012   946574.3   3254837.7          .   .01507882 |
 26. |    62   2013   189954.6   2438734.6          .   .01395404 |
 27. |    62   2014   423862.8   1904046.2          .   .01103734 |
 28. |    62   2015          0   390097.94          .           . |
     |------------------------------------------------------------|
 29. |    71   2009          0           0   .0075024   .00750235 |
 30. |    71   2010          0           0   .0105141    .0090082 |
 31. |    71   2011          0           0   .0064513   .00815591 |
 32. |    71   2012   393.2239   98.305977   .0062962   .00769098 |
 33. |    71   2013    12818.8   3303.0064   .0072857   .00763681 |
 34. |    71   2014   21111.51   8580.8829   .0115804   .00790338 |
 35. |    71   2015   33907.65   17057.796   .0090385   .00855018 |
     +------------------------------------------------------------+

You must install rangestat before you can use it:

ssc install rangestat