average of previous years

[Ema Davies]

Hello,

I am working with quarterly panel data and I need to compute the mean rank for each quarter over the past 5 years. My data look like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input long gvkey byte quarter float(yq ebitdaq rank)
1001 1  92  .284   4
1001 2  93   .87   2
1001 3  94  .805   3
1001 4  95 1.065   1
1001 1  96  .609   4
1001 2  97  1.02   1
1001 3  98  .953   2
1001 4  99   .69   3
1001 1 100  .844   4
1001 2 101 1.317   3
1001 3 102  2.73   2
1001 4 103 3.122   1
1001 1 104  1.48   1

Each quarter in a given year has a rank from 1 to 4. I need to compute the average rank of each quarter over the previous 5 years. I would really appreciate if someone could help me with this. Thanks!

[Nick Cox]

rangestat is from SSC and must be installed before you can use it. The previous 5 years are the previous 20 quarters.

* Example generated by -dataex-. For more info, type help dataex
clear
input long gvkey byte quarter float(yq ebitdaq rank)
1001 1  92  .284   4
1001 2  93   .87   2
1001 3  94  .805   3
1001 4  95 1.065   1
1001 1  96  .609   4
1001 2  97  1.02   1
1001 3  98  .953   2
1001 4  99   .69   3
1001 1 100  .844   4
1001 2 101 1.317   3
1001 3 102  2.73   2
1001 4 103 3.122   1
1001 1 104  1.48   1
end 

rangestat (count) count=rank (mean) mean=rank, int(yq -20 -1) by(gvkey quarter)

sort quarter yq

list, sepby(quarter) 

     +-------------------------------------------------------+
     | gvkey   quarter    yq   ebitdaq   rank   count   mean |
     |-------------------------------------------------------|
  1. |  1001         1    92      .284      4       .      . |
  2. |  1001         1    96      .609      4       1      4 |
  3. |  1001         1   100      .844      4       2      4 |
  4. |  1001         1   104      1.48      1       3      4 |
     |-------------------------------------------------------|
  5. |  1001         2    93       .87      2       .      . |
  6. |  1001         2    97      1.02      1       1      2 |
  7. |  1001         2   101     1.317      3       2    1.5 |
     |-------------------------------------------------------|
  8. |  1001         3    94      .805      3       .      . |
  9. |  1001         3    98      .953      2       1      3 |
 10. |  1001         3   102      2.73      2       2    2.5 |
     |-------------------------------------------------------|
 11. |  1001         4    95     1.065      1       .      . |
 12. |  1001         4    99       .69      3       1      1 |
 13. |  1001         4   103     3.122      1       2      2 |
     +-------------------------------------------------------+

[Ema Davies]

rangestat is from SSC and must be installed before you can use it. The previous 5 years are the previous 20 quarters.

* Example generated by -dataex-. For more info, type help dataex
clear
input long gvkey byte quarter float(yq ebitdaq rank)
1001 1 92 .284 4
1001 2 93 .87 2
1001 3 94 .805 3
1001 4 95 1.065 1
1001 1 96 .609 4
1001 2 97 1.02 1
1001 3 98 .953 2
1001 4 99 .69 3
1001 1 100 .844 4
1001 2 101 1.317 3
1001 3 102 2.73 2
1001 4 103 3.122 1
1001 1 104 1.48 1
end

rangestat (count) count=rank (mean) mean=rank, int(yq -20 -1) by(gvkey quarter)

sort quarter yq

list, sepby(quarter)

+-------------------------------------------------------+
| gvkey quarter yq ebitdaq rank count mean |
|-------------------------------------------------------|
1. | 1001 1 92 .284 4 . . |
2. | 1001 1 96 .609 4 1 4 |
3. | 1001 1 100 .844 4 2 4 |
4. | 1001 1 104 1.48 1 3 4 |
|-------------------------------------------------------|
5. | 1001 2 93 .87 2 . . |
6. | 1001 2 97 1.02 1 1 2 |
7. | 1001 2 101 1.317 3 2 1.5 |
|-------------------------------------------------------|
8. | 1001 3 94 .805 3 . . |
9. | 1001 3 98 .953 2 1 3 |
10. | 1001 3 102 2.73 2 2 2.5 |
|-------------------------------------------------------|
11. | 1001 4 95 1.065 1 . . |
12. | 1001 4 99 .69 3 1 1 |
13. | 1001 4 103 3.122 1 2 2 |
+-------------------------------------------------------+

Thanks a lot!