# st: Calculate rolling mean using last observation in a group

 From "Marietta Jones" To statalist@hsphsun2.harvard.edu Subject st: Calculate rolling mean using last observation in a group Date Mon, 11 Aug 2008 23:07:30 +0100

```Dear Statalist Users,

I have a new problem related to the dataset I presented in my original
email (see below).

For each observation in the dataset, I would like to calculate the
mean of variable x for the same company_no on a rolling prior 90 day
basis.
The extra complexity arises by the fact that there may be more than
one observations of variable x for the same investor_id for a given
company_no.
I would only like to include the last observation per investor_id for
a given company_no in the calculation of the mean.

I would appreciate any suggestions on how to solve this problem.

Thanks,

Marietta

>
>> Marietta Jones
>>
>> I have the following dataset:
>>
>> Company_no     Date          Investor_id
>> 1                     03/01/2000          1
>> 1                     04/09/2000          1
>> 1                     12/12/2001          2
>> 2                     13/12/2000          4
>> 2                     07/08/2001          7
>> 3                     09/08/2000          4
>> 3                     19/03/2001          4
>> 3                     02/05/2001          5
>> 3                     03/12/2001          6
>>
>>
>> For each observation, I would like to calculate the number of distinct
>> investors owning shares in a given company in the previous 12 months.
>> The new variable (No_investors_past_12_months) will look as follows:
>>
>> Company_no       Date        Investor_id   No_investors_past_12_months
>> 1                     03/01/2000         1                     1
>> 1                     04/09/2000         1                     1
>> 1                     12/12/2001         2                     1
>> 2                     13/12/2000         4                     1
>> 2                     07/08/2001         7                     2
>> 3                     09/08/2000         4                     1
>> 3                     19/03/2001         4                     1
>> 3                     02/05/2001         5                     2
>> 3                     03/12/2001         6                     3
>>
>> For example, for company 2 on 07/08/2001 there are two investors who
>> have owned shares of this company within the past 12 months.
>>
>> The idea is that the same investor should be counted only once and we
>> should count all observations over the past 12 months, including the
>> current observation date. If there are no observations over the past
>> 12 months, the value of the new variable should equal 1.
>
> *
> *   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/
>
*
*   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/
```