Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.

# Re: st: averaging previous observations in panel data with missing observations

 From Scott Merryman To statalist@hsphsun2.harvard.edu Subject Re: st: averaging previous observations in panel data with missing observations Date Tue, 24 Jul 2012 07:09:53 -0500

```-tssmooth ma-

For example:

. webuse grunfeld,clear

. set seed 554321

. replace invest = . if runiform()< .33
(53 real changes made, 53 to missing)

. tssmooth ma invest2 = invest, w(10)
The smoother applied was
by company : (1/10)*[x(t-10) + x(t-9) + x(t-8) + x(t-7) + x(t-6)
+ x(t-5) + x(t-4) + x(t-3) + x(t-2) + x(t-1) + ...; x(t)= invest

. l com year invest* if com == 1

+------------------------------------+
| company   year   invest    invest2 |
|------------------------------------|
1. |       1   1935        .          . |
2. |       1   1936    391.8          . |
3. |       1   1937        .      391.8 |
4. |       1   1938    257.7      391.8 |
5. |       1   1939        .     324.75 |
|------------------------------------|
6. |       1   1940    461.2     324.75 |
7. |       1   1941        .   370.2333 |
8. |       1   1942        .   370.2333 |
9. |       1   1943        .   370.2333 |
10. |       1   1944    547.5   370.2333 |
|------------------------------------|
11. |       1   1945        .     414.55 |
12. |       1   1946    688.1     414.55 |
13. |       1   1947    568.9    488.625 |
14. |       1   1948    529.2     504.68 |
15. |       1   1949    555.1     558.98 |
|------------------------------------|
16. |       1   1950        .   558.3333 |
17. |       1   1951        .     577.76 |
18. |       1   1952    891.2     577.76 |
19. |       1   1953   1304.4        630 |
20. |       1   1954        .   726.3428 |
+------------------------------------+

Scott

On Mon, Jul 23, 2012 at 9:20 PM, Mike McDonald <mimcdonald03@gmail.com> wrote:
> Hi Statalisters:
>
> I have panel data across countries for each year 1960-2010. For each
> country/year, I want to create a variable, y_avg, that is the average
> of that variable over the past 10 years. I know I can do this manually
> like this:
>
> gen y_avg = (L1.x + L2.x + ... + L10.x) / 10
>
> The problem is that there is missing data all over the place, so this
> breaks down for any 10-year period with missing observations.
>
> I have seen some other suggestions in searching the statalist archive
> -- for instance using egen with filter() -- but it is not clear to me
> that any of these ideas overcome the missing data problem.
>
> Can anyone suggest an easy way to accomplish what I want, ignoring
> missing observations in each average?
>
> Thanks!
>
> Mike McDonald
> University of Maryland
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
```