using multiple if statements to generate a variable

[Danielle leblanc]

Hi,
I want to generate an average of roeend for the last 4 quarters for the firms that have the same rssd9001 and same statefips and sz_large==1. How can I do so?

numdate quarterly qdate = year quarter, pattern(YQ)
xtset rssd9001 qdate

the data looks like this

[Carlo Lazzaro]

Danielle:
elaborating on your previous data excerpt:

. egen flag=group(rssd9001 statefips year) if sz_large==1

. sort year (quarter)

. gen double wanted=.

. forval flag = 1/5 {
  2. egen d= mean(roeend) if flag==`flag'
  3. replace wanted=d if flag==`flag'
  4. drop d
  5. }

. list rssd9001 statefips year quarter wanted if flag!=.

     +--------------------------------------------------+
     | rssd9001   statef~s   year   quarter      wanted |
     |--------------------------------------------------|
 19. |     2648         17   1995         3   .02744938 |
 21. |     2648         17   1995         4   .02744938 |
 23. |     2648         17   1996         1    .0668738 |
 25. |     2648         17   1996         2    .0668738 |
 28. |     2648         17   1996         3    .0668738 |
     |--------------------------------------------------|
 29. |     2648         17   1996         4    .0668738 |
 32. |     2648         17   1997         1   .08051372 |
 33. |     2648         17   1997         2   .08051372 |
 35. |     2648         17   1997         3   .08051372 |
 37. |     2648         17   1997         4   .08051372 |
     |--------------------------------------------------|
 39. |     2648         17   1998         1   .07095233 |
 41. |     2648         17   1998         2   .07095233 |
 44. |     2648         17   1998         3   .07095233 |
 46. |     2648         17   1998         4   .07095233 |
 48. |     2648         17   1999         1   .03812476 |
     +--------------------------------------------------+

.

[Nick Cox]

One command that can be useful here is rangestat from SSC. Search the forum for other mentions. You need a single quarterly date variable first.

This continues your helpful data example. If "last 4 quarters" means "previous 4 quarters" the offset should be -4 -1 not -3 0.

gen qdate = yq(year, quarter)

rangestat (count) roeend (mean) roeend if sz_large==1, int(qdate -3 0) by(rssd9001 statefips)

list rssd9001 statefips qdate roe* if sz_large

     +---------------------------------------------------------------+
     | rssd9001   statef~s   qdate     roeend   roeend~t   roeend_~n |
     |---------------------------------------------------------------|
 73. |     2648         17     142   .0158901          1   .01589014 |
 74. |     2648         17     143   .0390086          2   .02744938 |
 75. |     2648         17     144   .0107903          3   .02189636 |
 76. |     2648         17     145   .0477364          4   .02835635 |
 77. |     2648         17     146   .0850639          4   .04564979 |
     |---------------------------------------------------------------|
 78. |     2648         17     147   .1239047          4    .0668738 |
 79. |     2648         17     148   .0329447          4   .07241241 |
 80. |     2648         17     149   .0622058          4   .07602976 |
 81. |     2648         17     150   .0956651          4   .07868007 |
 82. |     2648         17     151   .1312392          4   .08051371 |
     |---------------------------------------------------------------|
 83. |     2648         17     152   .0316611          4   .08019282 |
 84. |     2648         17     153   .0626878          4   .08031332 |
 85. |     2648         17     154   .0951975          4   .08019642 |
 86. |     2648         17     155   .0942629          4   .07095233 |
 87. |     2648         17     156   .0381248          4   .07256823 |
     +---------------------------------------------------------------+

[Danielle leblanc]

many thanks.that is very helpful