Rolling average for non-missing required observation

[Omar Alotaibi]

Hi to everyone

The short:
How can I ensure that a company has at least 8 yearly observations in my sample?


The long:
I am conducting a study on listed companies. My sample is comprised of listed companies observations over a number of years. So each observation is a company-year observation and each observation is identified by the company code and the year. I am trying to calculate a proxy composed of six rolling averages over a prior five-year window. The calculation requires that:
1. each company is listed for at least 6 years in order to calculate the prior five-year rolling averages
2. in order to be included in the sample, each company must be represented by 3 company-year observations after calculating the proxy.

So for example, in order to include company A at time T observation in the sample, it has to have observations at times T-1, T-2, T-3, T-4, and T-5 in order to calculate the rolling average. Then, it has to have another two observations (at time T+1 and T+2) in order to satisfy the second condition.

Is there a code I can use to ensure the presence of eight yearly observations for each company in my sample?

[Nick Cox]

See rangestat from SSC.

rangestat (count) count1=whatever, int(year -5 -1), by(company) 

rangestat (count) count2=whatever, int(year 1 2), by(company)

counts non-missing values of whatever in the two windows mentioned and there is a similar command for non-missing values in total.

[Attaullah Shah]

ssc install asrol 
 bys company : asrol proxy, stat(average) window(year -6 -1) min(6)

[Attaullah Shah]

There is a typo in # 3. The correct code would be:

 bys company : asrol proxy, stat(mean) window(year -6 -1) min(5)

Here is one example

webuse grunfeld
bys company : asrol invest , stat(mean) window(year -6 -1) min(5)

. list company year invest mean_6_invest in 1/10

     +------------------------------------+
     | company   year   invest   mean_6~t |
     |------------------------------------|
  1. |       1   1935    317.6          . |
  2. |       1   1936    391.8          . |
  3. |       1   1937    410.6          . |
  4. |       1   1938    257.7          . |
  5. |       1   1939    330.8          . |
     |------------------------------------|
  6. |       1   1940    461.2      341.7 |
  7. |       1   1941      512     370.42 |
  8. |       1   1942      448     394.46 |
  9. |       1   1943    499.6     401.94 |
 10. |       1   1944    547.5     450.32 |
     +------------------------------------+

[Omar Alotaibi]

Thank you Nick for your code

[Omar Alotaibi]

Thank you Attaullah for your code.

However, when I tried it, it seems there is a missing condition.


bys gvkey : asrol SGA , stat(mean) window(fyear -6 -1) min(5)


missing_Min_noFocal(): 3001 expected 8 arguments but received 9
asrolfw(): - function returned error
<istmt>: - function returned error

[Attaullah Shah]

Please install the beta version of asrol from my site.

net install asrol, from(http://fintechprofessor.com) replace

If the problem persists, then email ([email protected]) a sample dataset that can reproduce the error.