Re: st: annual and 3years average

[Neil Shephard <nshephard@nhs.net>]

Fabian Brenner wrote:
> Dear all,
>
> I have several observations called "ROE" for the "years" (from 1979 to 2006). There is a different number of observations for each year. 
> My data look like this:
> "year"   "ROE"      "Average"   "threeyearaverage"
>
> 79           12               ?                       ?      
> 79            9                ?                       ?
> 79            2                ?                       ?
> 80            3                ?                       ?
> 81            20              ?                       ?
> 81            5                ?                       ?
> 82            3                ?                       ?
> 82            6                ?                       ?
> 82            9                ?                       ?
> 82            8                ?                       ?
> .               .                 .
> .               .                 .
> .               .                 .
>
> I want to compute the average of the observations for each year, e.g. for 1979: (12+9+2)/3 (I tried to sort the observations and to divide the sum by _n but it didn't work...)
>   
bysort year : egen average = mean(ROE)
> In a second step I want to get the average ROE for the past three years ("threeyearaverage") (beginning in 1982), e.g. for 1982 it should be the average of the ROE in 1979 plus the average ROE in 1980 plus average ROE in 1981, divided by 3.
>   
Thats an inappropriate way of calculating the three year average, as it 
fails to account for the fact that there are different numbers 
observations from each year, thus the weights aren't equal.  This is 
covered in most basic statistics books.  You therefore have two options, 
a) use weights; b) use the raw data.  Since -egen newvar = mean()- 
doesn't allow weights I'd be inclined to go with b).

You therefore need to generate a variable that bins your data...

gen year3 = .
replace year3 = 1 if(year >= 79 & year <= 82)
replace year3 = 2 if(year >= 83 & year <= 85)
replace year3 = 3 if(year >= 86 & year <= 88)
....
bysort year3 : egen threeyearaverage = mean(ROE)

Neil

***********************************************************************
This  message  may  contain  confidential and  privileged  information.
If you  are not the  intended recipient  you should not  disclose, copy
or distribute information in this e-mail or take any action in reliance
on its contents.  To do so is strictly  prohibited and may be unlawful.
Please  inform  the  sender that  this  message has  gone astray before
deleting it.  Thank you.

2008 marks the 60th anniversary of the NHS.  It's an opportunity to pay
tribute to the NHS staff and volunteers who help shape the service, and
celebrate their achievements.

If you work for the NHS  and  would like  an NHSmail  email account, go
to: www.connectingforhealth.nhs.uk/nhsmail
***********************************************************************

*
*   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/