Average (Mean) of previous quarters

[Steven Sanders]

Hello everyone,

I have the following problem with my unbalanced monthly panel data, where "permno" is the identifier for a company: I want to generate a new variable that captures the mean of the previous three quarters (in the next step I want to calculate the deviation from the mean, but that is not part of my question). If there is no data for the last three previous quarters available, I want the new variable to have a missing value. To give more context, the variable "cheq_new" is the quarterly accounting number Cash and Short-Term Investments. Because the data is published quarterly, each month per quarter has the same value.

Here is an example of the data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno float(number year month quarter cheq_new)
10000  1 1985 12 4     .
10000  2 1986  1 1  .743
10000  3 1986  2 1  .743
10000  4 1986  3 1  .743
10000  5 1986  4 2  .395
10000  6 1986  5 2  .395
10000  7 1986  6 2  .395
10000  8 1986  7 3  .425
10000  9 1986  8 3  .425
10000 10 1986  9 3  .425
10000 11 1986 10 4  .348
10000 12 1986 11 4  .348
10000 13 1986 12 4  .348
10000 14 1987  1 1     .
10000 15 1987  2 1     .
10000 16 1987  3 1     .
10000 17 1987  4 2  .341
10000 18 1987  5 2  .341
10000 19 1987  6 2  .341
10001  1 1985 12 4     .
10001  2 1986  1 1  .106
10001  3 1986  2 1  .106
10001  4 1986  3 1  .106
10001  5 1986  4 2  .746
10001  6 1986  5 2  .746
10001  7 1986  6 2  .746
10001  8 1986  7 3  .775
10001  9 1986  8 3  .775
10001 10 1986  9 3  .775
10001 11 1986 10 4  .649
10001 12 1986 11 4  .649
10001 13 1986 12 4  .649
10001 14 1987  1 1  .837
10001 15 1987  2 1  .837
10001 16 1987  3 1  .837
10001 17 1987  4 2  .729
10001 18 1987  5 2  .729
10001 19 1987  6 2  .729
10001 20 1987  7 3  .173
10001 21 1987  8 3  .173
10001 22 1987  9 3  .173
10001 23 1987 10 4  .754
10001 24 1987 11 4  .754
10001 25 1987 12 4  .754
10001 26 1988  1 1  .731
10001 27 1988  2 1  .731
10001 28 1988  3 1  .731
10001 29 1988  4 2 1.206
10001 30 1988  5 2 1.206
10001 31 1988  6 2 1.206
10001 32 1988  7 3 1.203
10001 33 1988  8 3 1.203
10001 34 1988  9 3 1.203
10001 35 1988 10 4 1.051
10001 36 1988 11 4 1.051
10001 37 1988 12 4 1.051
10001 38 1989  1 1 1.206
10001 39 1989  2 1 1.206
10001 40 1989  3 1 1.206
10001 41 1989  4 2 1.177
10001 42 1989  5 2 1.177
10001 43 1989  6 2 1.177
10001 44 1989  7 3  .731
10001 45 1989  8 3  .731
10001 46 1989  9 3  .731
10001 47 1989 10 4  .784
10001 48 1989 11 4  .784
10001 49 1989 12 4  .784
10001 50 1990  1 1  .756
10001 51 1990  2 1  .756
10001 52 1990  3 1  .756
10001 53 1990  4 2 1.356
10001 54 1990  5 2 1.356
10001 55 1990  6 2 1.356
10001 56 1990  7 3 1.025
10001 57 1990  8 3 1.025
10001 58 1990  9 3 1.025
10001 59 1990 10 4  .583
10001 60 1990 11 4  .583
10001 61 1990 12 4  .583
10001 62 1991  1 1 1.164
10001 63 1991  2 1 1.164
10001 64 1991  3 1 1.164
10001 65 1991  4 2 1.595
10001 66 1991  5 2 1.595
10001 67 1991  6 2 1.595
10001 68 1991  7 3  .566
10001 69 1991  8 3  .566
10001 70 1991  9 3  .566
10001 71 1991 10 4  1.17
10001 72 1991 11 4  1.17
10001 73 1991 12 4  1.17
10001 74 1992  1 1 1.341
10001 75 1992  2 1 1.341
10001 76 1992  3 1 1.341
10001 77 1992  4 2   1.1
10001 78 1992  5 2   1.1
10001 79 1992  6 2   1.1
10001 80 1992  7 3  3.89
10001 81 1992  8 3  3.89
end

Best regards,
Steven

[Wouter Wakker]

This requires rangestat from SSC:

* ssc install rangestat // remove asterisk if not already installed
gen yq = yq(year, quarter)
format yq %tq
rangestat (mean) wanted=cheq_new, interval(yq, -3, -1) by(permno)

[Nick Cox]

I would add that the repetition of quarterly data 3 times is only a good idea if you also have variables varying monthly that you are not telling us about.

[Steven Sanders]

Thank you very much!

Yes, the data also includes monthly return data for every firm. My goal is to form a cross-sectional index (percentiles) based on deviations of different accounting data.