Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down on April 23, and its replacement, statalist.org is already up and running.

# Re: st: calculating average in Stata

 From Nick Cox To statalist@hsphsun2.harvard.edu Subject Re: st: calculating average in Stata Date Wed, 6 Mar 2013 11:21:09 +0000

```This code is awkward but appears to be what you want

* sand pit to play in
webuse grunfeld, clear
replace invest = . if year == 1952
tsset

gen double numer = max(L1.invest, 0) + max(L2.invest, 0) + max(L3.invest, 0)
gen denom = (L1.invest < .) + (L2.invest < .) + (L3.invest < .)
gen avelast3 = numer/denom

There is a presumption that -invest- (in your case, -sales- cannot be
negative. For a variable that could be negative, use (e.g.)

cond(missing(L1.invest), 0, L1.invest)

rather than

max(L1.invest, 0)

For many more than 3 previous times, you would be better off with a
loop. For e.g. 13 not 3

gen double numer = 0
gen denom  = 0

qui forval j = 1/13 {
replace numer = numer + max(L`j'.invest, 0)
replace denom = denom + (L`j'.invest < .)
}

P.S. I advise against the form "a ... data" and recommend "a ...
dataset". The former jars with many writers and speakers of English as
non-standard usage.

On Wed, Mar 6, 2013 at 6:17 AM, James Bernard <jamesstatalist@gmail.com> wrote:

> I am trying to calculate average of observations for a variable. This
> is a panel data... grouped by firms observed over 20 years.
>
> Now, I want to calculate average of past three years of a variable to
> construct a new variable.
>
> Firm                  Year                sales
> --------           ------------              --------------
> A                 1990                    100
> A                  1991                    missing
> A                  1992                    200
> A                  1993
> A                   1994
> A                   1995
>
>
> So i would like to create a new average_3_sales variable that take in
> year 1993 the average of sales observation from year 1990 to 1992....
> What Stata does is that it simply returns missing as 1991 observation
> is missing. Is there any solution to this?
>
> In my research it would be more reasonable to ignore the missing value
> and calculate average of 100 and 200 which is 150. so the value of the
> new variable would be 150 for year 1993.
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/
```