moving average/tsgen

[Galal Ali]

Hi All,

I have a monthly panel data for which I want to get the average of x variable from the last year (-12 months) up to the end of the following year (+ 12 months), with a minimum of one observation on both sides (lead and lag).

Any advice on this?

G

[Nick Cox]

No data example (FAQ Advice #12). You would like windows of length 25 but would settle for 3 with 1 value before and 1 value after a particular time.

I guess by tsgen you mean tsegen (SSC). You are asked to explain community-contributed commands you refer to (FAQ Advice #12 again).

This code shows some principles. rangestat is also from SSC.

webuse grunfeld , clear
gen ln_invest = ln(invest)
rangestat  (count) wanted1=ln_invest, int(year -1 1) by(company)
rangestat  (count) wanted2=ln_invest (mean) wanted3 = ln_invest, int(year -5 5) by(company)
list ln_invest wanted? year if company == 1, sepby(wanted1)

     +-------------------------------------------------+
     | ln_inv~t   wanted1   wanted2     wanted3   year |
     |-------------------------------------------------|
  1. | 5.760793         2         6   5.8727176   1935 |
     |-------------------------------------------------|
  2. | 5.970751         3         7   5.9249472   1936 |
  3. |  6.01762         3         8   5.9474279   1937 |
  4. | 5.551796         3         9   5.9770257   1938 |
  5. | 5.801514         3        10   6.0098593   1939 |
  6. | 6.133832         3        11     6.03897   1940 |
  7. | 6.238325         3        11   6.1092556   1941 |
  8. | 6.104793         3        11   6.1431605   1942 |
  9. | 6.213808         3        11   6.1662284   1943 |
 10. | 6.305362         3        11   6.2359877   1944 |
 11. | 6.330077         3        11   6.2963945   1945 |
 12. | 6.533934         3        11   6.3413106   1946 |
 13. | 6.343705         3        11   6.3916964   1947 |
 14. | 6.271367         3        11   6.4888515   1948 |
 15. | 6.319148         3        11   6.5879884   1949 |
 16. | 6.465989         3        10    6.616251   1950 |
 17. | 6.627909         3         9   6.6480481   1951 |
 18. | 6.792569         3         8   6.6623124   1952 |
 19. | 7.173499         3         7   6.7078278   1953 |
     |-------------------------------------------------|
 20. | 7.304314         2         6   6.7805713   1954 |
     +-------------------------------------------------+

For regularly spaced data (no gaps) with no missing values, minimally acceptable windows are for every period except the first and last in each panel. With a window of length 11 that means a count of 7 or more values present. With a window of length 25 that means a count of 14 or more values present. WIth regularly spaced data with no missing values your code could look something like

rangestat  (count) count=x (mean) mean=x, int(mdate -12 12) by(panelid)

and you then can ignore observations with count < 14 if you wish. If you have gaps or missing values, the two-fold solution will prove easier to think about.