"tsegen" by group

[Francois Durant]

Hi,

I would like to calculate the moving average of _b_LogSize _b_LogBM _b_MOM12 _b_cons by months of the year over the last 10 years. For instance, I would like to average:
[_b_LogSize(Jan2020)+_b_LogSize(Jan2019)+... + _b_LogSize(Jan2010)]/10
I would like to do it for each month of the year and the 4 variables above.

Currently, I am using "tsegen" but it is only performing a basic 10 years rolling average without taking into account months. Please can you help? Thanks!

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(date _b_LogSize _b_LogBM _b_MOM12 _b_cons month)
493    .09046634   4.1176205  2.5190666 -1.2977983  2
494   -2.0309033   .22475155  -9.759151  30.883093  3
495   -1.0832306     .406494  1.2204374   12.85686  4
496    -2.059664  -.24260585 -2.1624966  17.363857  5
497     .0960215   1.4607705   .5428929  1.1389401  6
498   -.51043904   1.9077493   2.975317  3.2279406  7
499    1.4333166   1.5936514   8.690335 -22.944815  8
500    -3.742916  -2.0467317 -10.620685   37.35859  9
501   -1.5358274  -.21344493  -11.50268   21.56925 10
502   -1.3188688   1.5866338  -1.571547  17.318272 11
503     .4737448   1.0378938   5.982695  -3.458642 12
504     .6831618   2.8478656  2.8225315 -3.0741665  1
505    -1.461657  -.15056618  -7.833986   19.39885  2
506    .15322176    3.042314   8.148885 -1.4166797  3
507    1.3517747   1.4202607   3.542518  -12.55804  4
508     .5102485    2.402631   9.466259   -8.92338  5
509    .14730057  -2.0097933  3.9253964 -12.569436  6
510   -.13011447   2.1992843 -1.5208116  4.3657007  7
511    -.7876962  -1.2948427  12.139635  -1.489981  8
512    .11407951   -.9536515 -10.651284   3.702368  9
513   -3.1914134    3.209994  -19.96686    39.4386 10
514      .485197   2.1473858  10.600106  -6.581267 11
515    -.3188047    .3255054 -1.9797972   .2980764 12
516     .1177141   -.3202377  1.3013583 -2.6397576  1
517   -1.1778133   1.3565993  -.6570513  12.859035  2
518   -.06110432    .7163003  -13.77926   7.564542  3
519    -3.706227    .1257015   -21.8051   38.16873  4
520   .013643764    .7770253  -.7633198  1.8524495  5
521    -2.111613   -.4619206 -2.8396015  22.133587  6
522    -.6336941     .363702   1.210958   9.975827  7
523    -1.014817    .5368967  -2.220904   9.022312  8
524    -.5406848   -.6548046   4.404526  10.582654  9
525    -.7146962   .26931196 -1.1149786   9.302611 10
526     .6385671   2.2701182 -1.6060712  1.2781098 11
527    -.7188457   .57633734  3.1786885    8.57283 12
528    -.4600639    .9384994  -2.588896   8.649567  1
529    -.8613429    .9671511   1.099575   8.608756  2
530   -.07247534   -.1537994 -4.3157163   .8893291  3
531   -.56535167   .07716002   3.332622   5.861691  4
532    -.4648149    1.094499  2.3134766   7.434675  5
533    .27500623   2.5805945 -2.3379977   -2.46115  6
534   -.12708639   1.0634447  -2.841303   3.477974  7
535    -.8708178   .23912176  3.3763766  10.540322  8
536   -.54018456    .7189706 .036027018   8.435553  9
537   -1.4168496   1.6745526   3.554173  19.626165 10
538    -.8309463  -1.2100756  -3.173917  11.207634 11
539     .4382522   1.5968238  3.6161275  -5.716834 12
540    .09637992   2.2883317   2.479093    3.72967  1
541    -.2498437    1.620358   .7389261   2.624726  2
542    -.4497205  -.22138956  -4.454505  2.4982936  3
543   -1.2707784    .2922744  2.3344254  16.399544  4
544   -1.0866243   1.5483017    4.93187  12.820357  5
545    -.6698218   -.3213176  1.0515611  11.332736  6
546   -.04462296   1.1982578  4.1905856  .09844476  7
547 -.0022533806   .58677596  4.4705863   .9089295  8
548   -.26722765   -2.316635 -1.8966275  -.8938839  9
549    -.7312025   -1.262661  2.9972234   9.407964 10
550   -.25243318    .7990872   2.407081    3.20534 11
551   -1.7431985   -.8312908   8.716989  18.789204 12
552    -.6822688  -.11647125  -5.275173   7.973406  1
553    -1.329878   -.7738516   2.598626  13.546588  2
554     .6247546   1.2956816   .6266102  -3.531033  3
555  -.072735175 -.015340515  -3.187542  -1.636328  4
556    .23074944   -.3995629  2.8622246  -2.874943  5
557     .8129722    3.236957  -.8909066 -4.6846304  6
558    -.6832631  -1.0202931  -7.254835   9.369593  7
559   -.58940864  -1.5918627 -4.2435346    7.06904  8
560   -1.2256618   -.6868519  -.5801456  15.051693  9
561    -.8219826    .3530865   -4.62138   11.96707 10
562     .6590861    1.541397  2.6057794  -4.902538 11
563    -.8354015   -.8621215   2.812942   9.531097 12
564    -.9848793   1.3190557 -1.2905746   10.60272  1
565     .1217109   .48999825  1.0703242  .04173471  2
566     .1686769   -.4838383 -1.5908992  1.8760736  3
567   -.05383684    .5829975 -.28491843   4.785616  4
568    .03835373  -.19093046 -.13494432 -2.3227618  5
569    .23461634  -2.2530422  -.7020459  -7.838483  6
570    -.4224021   -.7332852     .83166   4.854007  7
571     .7889878   -.4763553   6.132439  -5.976163  8
572    -.4769273   .21490236   5.495591   6.372781  9
573     .6893386    .7729292   2.860333 -10.344966 10
574   -.12302468    .3361884   7.454767  -.4429162 11
575   -.11429738    3.273526 -10.505454    .959588 12
576    -.8703886    .2214929    7.45836   5.192826  1
577    .08055088   -.8899767 -1.0208174 -1.7982605  2
578   -1.0253339    .5415361   4.950213   16.10081  3
579    -1.318757   -.4902536   4.123805  15.095932  4
580   -.04682831  -1.1088916  11.664658 -10.094048  5
581   -1.4003263   1.8397713   -5.47766    16.4739  6
582    -.8646321   .17430896  -8.737585  10.405688  7
583    -.5431101   2.2606583 -1.1048132  -.7501085  8
584     .9161993    .5219336   2.973937  -27.47846  9
585       .62261   -.6762373   4.851254  -14.31035 10
586   -1.4068698   1.9171596  -9.508333  15.039592 11
587    -.7277468  -2.5405514    5.02278 -1.5999113 12
588    -.4634641  -3.9733584  -.3435096  -8.650168  1
589     .1495674  -.18204223  -20.42709  1.6548795  2
590      -4.0047   1.1798627  -55.92088  30.824076  3
591     1.038709    3.684857 -13.146856  -6.878941  4
592     -.822459    .1430246   9.767742  11.020597  5
end
format %tm date

local t 1
local model1 "LogSize LogBM MOM12"
    *** 10 year rolling window ***
        tsegen r_cons = rowmean(L(1/120).(_b_cons))
        drop _b_cons
        foreach var in `model`t''{
            tsegen r_`var' = rowmean(L(1/120).(_b_`var'))
            drop _b_`var'
        }

[Nick Cox]

The complaint here seems to be that tsegen (from SSC, as you are asked to explain: FAQ Advice #12) doesn't do something you never asked it to do. Your syntax averages over the previous ten years (i.e. up to 120 observations) and says nothing about month of the year.

I think this is easier with rangestat (also from SSC).

Thanks for your data example, which can be followed by

ssc install rangestat

gen year = year(dofm(date))

rangestat _b* , int(year -10 -1) by(month)

[Francois Durant]

Thanks a lot Nick, it really helps.

Also, using the previous data I provided, it seems like "tsegen" does not work with more than 100 lags. Am I doing something wrong?

*** all is fine 
 tsegen r_cons = rowmean(L(1/101).(_b_cons))  *** does not work tsegen r_cons = rowmean(L(1/100).(_b_cons))  invalid numlist

[Francois Durant]

Is there a way to adapt the code you proposed to generate the past 3 months average over the last 10 years? Thank you in advance.

[Nick Cox]

Thanks for the problem report. I am an author of tsegen, but I haven't used it in a while. We'll look into that report.

EDIT:

I can't reproduce the problem.

. clear

. set obs 1000
number of observations (_N) was 100, now 1,000

. gen t = _n

. tsset t
        time variable:  t, 1 to 1000
                delta:  1 unit

. gen y = _n

. tsegen whatever = rowmean(L(1/100).y)
(1 missing value generated)

. tsegen whatever2 = rowmean(L(1/101).y)
(1 missing value generated)

.

[Nick Cox]

#4 Average over the last three months and then average over the last years. Here is an example. You can work with several variables at once, but you may need to do work some at their names.

. gen year = year(dofm(date))

. rangestat _b_LogSize, int(date -3 -1)

. rangestat wanted=_b_LogSize_mean, int(year -10 -1) by(year)

[Francois Durant]

I am getting missing values for the entire variable "wanted". Is that what you get too? Thanks.

[Nick Cox]

Sorry; as comparison with #2 should also make clear the code should be more like

gen year = year(dofm(date))
rangestat _b_LogSize, int(date -3 -1)
rangestat wanted=_b_LogSize_mean, int(year -10 -1) by(month)

[Francois Durant]

Thanks a lot for your response, again, it works well now!

I am trying different things to get more stable coefficients. Now, I would like to assess the stability of the t-statistics weighted average of the estimated beta coefficients on a rolling basis, say 5 months, in the following example to make it easy.

The code I created below does not output an error in Stata but it does not do what I wish since it does not create weighted coefficients that add up to 1 over the last 5 months. Would you be able to help? Thanks

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(date _b_LogSize t_LogSize abs_t_LogSize tsum_LogSize tw_LogSize)
480   -8.266647   -4.911039   4.911039         .          .
481    2.581961    2.566609   2.566609         .          .
482    1.255753    1.632749   1.632749         .          .
483    .8442108   1.1369855  1.1369855         .          .
484  -3.3915694  -3.5551646  3.5551646 13.802547  .25757307
485  -1.0104964  -1.3168218  1.3168218  10.20833  .12899484
486   -1.903375  -2.2898364  2.2898364  9.931558  .23056166
487    .4738106    .6090372   .6090372 8.9078455  .06837087
488   -.0876909  -.11545384  .11545384  7.886314 .014639772
489   1.1190147    1.424378   1.424378  5.755527     .24748
490  -1.2875426   -1.538554   1.538554  5.977259  .25740126
491   -4.066994   -3.895815   3.895815  7.583238   .5137403
492   -.3479997    -.489352    .489352  7.463553  .06556556
493     .777527   1.1554649  1.1554649  8.503564  .13588007
494   -3.302649  -3.8024635  3.8024635  10.88165   .3494382
495  -1.8951005   -3.518439   3.518439 12.861534   .2735629
496    -3.67513   -5.062582   5.062582   14.0283   .3608835
497   .07620832   .13785669  .13785669 13.676805 .010079597
498   -.4320115   -.9034046   .9034046 13.424746  .06729398
499    2.646034    3.619975   3.619975 13.242257   .2733654
500   -6.142633   -7.235136   7.235136 16.958954   .4266263
501   -2.587543   -3.311952   3.311952 15.208324  .21777233
502  -2.1349971  -4.4263663  4.4263663 19.496834     .22703
503    .4620627     .861635    .861635 19.455065  .04428846
504   1.2540562    2.224489   2.224489 18.059578  .12317502
505   -2.555257   -5.383336   5.383336 16.207779   .3321452
506   .13377367   .23725756  .23725756 13.133084 .018065639
507    1.979651   4.3510914  4.3510914  13.05781   .3332176
508    .6884593    1.247736   1.247736  13.44391   .0928105
509    .4770622    .6950083   .6950083  11.91443  .05833333
510   -.4007422   -.7961677   .7961677  7.327261   .1086583
511   -.9754319  -1.9351906  1.9351906  9.025194  .21442093
512    .6744914    .8957349   .8957349  5.569838  .16081886
513   -5.285298   -5.344624   5.344624  9.666725   .5528888
514    .4946819    .9643504   .9643504  9.936068  .09705554
515   -.3796268   -.7855311   .7855311  9.925431  .07914327
516 -.033686493   -.0726955   .0726955  8.062936 .009016009
517  -1.7550406   -3.186371   3.186371 10.353572   .3077557
518   -.3205542   -.6093335   .6093335  5.618281   .1084555
519   -5.138365   -7.682914   7.682914 12.336844   .6227617
520   -.3349997   -.8906261   .8906261  12.44194  .07158258
521   -2.908237   -5.094873   5.094873 17.464117  .29173377
522   -.7331108   -1.782251   1.782251 16.059998  .11097455
523   -1.350431   -3.309204   3.309204 18.759869  .17639804
524  -1.0057278  -2.1181622  2.1181622 13.195116  .16052623
525  -1.0435315  -2.6591425  2.6591425 14.963633    .177707
526    .6656214   1.9491335  1.9491335 11.817893   .1649307
527  -1.0886611    -2.61192    2.61192 12.647563   .2065157
528   -.6431454  -1.8611777  1.8611777 11.199536  .16618346
529  -1.2877115   -4.164419   4.164419 13.245793   .3143956
530  -.07760277  -.18290086  .18290086  10.76955 .016983146
531   -.7118146  -1.9242047  1.9242047 10.744623  .17908536
532   -.6422755  -2.0677242  2.0677242 10.200427  .20270957
533   .19157185    .5342802   .5342802  8.873529  .06021056
534   -.2652633   -.7714291   .7714291  5.480539  .14075789
535   -1.318615  -3.7864094  3.7864094  9.084047   .4168196
536   -.8856038  -2.0179932  2.0179932  9.177836   .2198768
537  -1.7783096   -5.388499   5.388499 12.498611  .43112785
538   -.9958586   -3.248513   3.248513 15.212844   .2135375
539    .6375813   1.9973515  1.9973515 16.438766  .12150252
540    .1909554    .5236455   .5236455 13.176003  .03974237
541  -.17884855    -.613762    .613762 11.771771  .05213846
542  -.55945045  -1.5065902  1.5065902  7.889862  .19095267
543   -1.655147   -4.741406   4.741406  9.382755  .50533193
544  -1.4834945    -4.56018    4.56018 11.945584   .3817461
545   -.9255059   -2.598146   2.598146 14.020084    .185316
546 -.022075985 -.072398596 .072398596  13.47872 .005371326
547    .1892124    .5711942   .5711942 12.543324   .0455377
548   -.3425543   -.9594861   .9594861  8.761405   .1095128
549   -.9611792   -2.651947   2.651947  6.853171  .38696635
550   -.6658006  -2.2426708  2.2426708  6.497696   .3451486
551   -2.314715   -5.489883   5.489883  11.91518   .4607469
552   -.6898829  -2.0486205  2.0486205 13.392607   .1529665
553   -1.823041   -5.690817   5.690817 18.123938   .3139945
554    .9535037    3.016883   3.016883 18.488874   .1631729
555   -.1609003   -.4754425   .4754425 16.721645 .028432757
556   .18754247    .6115062   .6115062  11.84327  .05163323
557   1.0243611    2.611286   2.611286 12.405935  .21048684
558   -.7195773  -2.1321523  2.1321523   8.84727   .2409955
559   -.8113478  -2.3689744  2.3689744  8.199362   .2889218
560  -1.6538635   -5.002859   5.002859 12.726778   .3930971
561  -1.0327011  -3.5210226  3.5210226 15.636294  .22518267
562   .54082316   2.1440485  2.1440485 15.169057  .14134356
563   -1.244109  -4.0540304  4.0540304 17.090935  .23720355
564  -1.0785816   -3.872051   3.872051 18.594011  .20824185
565    .1194961    .4390831   .4390831 14.030236  .03129549
566   .20267415    .6495556   .6495556  11.15877  .05821033
567  -.23517534   -.7762432   .7762432  9.790963   .0792816
568    .1605866   .56066215  .56066215  6.297596  .08902797
569    .2715707    .7454796   .7454796  3.171024  .23509115
570   -.6019359   -1.850141   1.850141  4.582082   .4037774
571     .744113   2.2486794  2.2486794  6.181205  .36379305
572   -.7933149   -1.911649   1.911649  7.316611   .2612752
573    .7634989   1.8988053  1.8988053  8.654755  .21939446
574   -.3443597   -.9029207   .9029207  8.812196  .10246262
575    -.299389   -.6598354   .6598354   7.62189   .0865711
576   -.8753999  -2.0526454  2.0526454  7.425856  .27641872
577   .08494118   .22103527  .22103527  5.735242  .03853983
578   -.8450174  -2.0233812  2.0233812  5.859818   .3452976
579   -1.264442  -3.2563105  3.2563105  8.213208   .3964724
end
format %tm date

    local t 1
    local m_periods 120
    local cutoff `m_periods'
        local model1 "LogSize " /*LogBM MOM12*/
        foreach var in `model`t''{
            gen t_`var' = _b_`var'/_se_`var'
            drop _se_`var'
            gen abs_t_`var'=abs(t_`var')
            mvsumm abs_t_`var', stat(sum) win(5) gen(tsum_`var') end
            gen tw_`var' = abs(t_`var')/tsum_`var'

        }
    browse date _b_LogSize t_LogSize abs_t_LogSize tsum_LogSize tw_LogSize

[Nick Cox]

A new question unrelated to the thread title deserves a new thread.

Otherwise, why the puzzlement here? mvsumm (from SSC, as you are asked to explain) with options stat(sum) w(5) creates sums for windows of length 5. No weights, and not a mean.

. clear

.. set obs 10
number of observations (_N) was 5, now 10

. gen t = _n

. tsset t
        time variable:  t, 1 to 10
                delta:  1 unit

. gen whatever = 42

.. mvsumm whatever, s(sum) w(5) generate(new)

. l

     +---------------------+
     |  t   whatever   new |
     |---------------------|
  1. |  1         42     . |
  2. |  2         42     . |
  3. |  3         42   210 |
  4. |  4         42   210 |
  5. |  5         42   210 |
     |---------------------|
  6. |  6         42   210 |
  7. |  7         42   210 |
  8. |  8         42   210 |
  9. |  9         42     . |
 10. | 10         42     . |
     +---------------------+

tssmooth ma sounds more like the solution you want to me. But the point about weighted averages is that the weights you use add to 1, not that the results do. Consider say Hanning where the weights are (1/4 1/2 1/4) for (previous present next). .

[Francois Durant]

Thanks for the response Nick, but I do not think it solves my problem.

I would like to weight the estimated beta coefficient by the t-statistics. The intuitions is that we want to give more importance to less noisy beta estimates (hence higher t-stat) in the rolling window average.

I would be happy to use tssmooth ma but I do not want to define fixed weight (for instance (1/4 1/2 1/4) as you suggested above). I would like to use the weight defined by the t-statistics as described above. Can you help me code that? Thanks a lot in advance.

[Nick Cox]

I think that really calls for a new thread, not least because there should be discussion on the merits and limitations of what you want to do as well as on how to do it. That discussion should best come from people who work on this kind of data.

[Itesh New]

I was interested in calculating inflation persistence for multiple countries which is usually calculated by AR(1) coefficients, and I calculated it using rangestat wherein I regressed inflation on its first lag for all the countries, “rangestat (reg) inflation inflation_lag , interval( Year -9 0) by(Country)
” however when I use the rolling option in stata for calculating rolling regression which is “rolling, window(10): arima inflation, ar(1) ” and doing it for all the countries separately since it does not allow the “by” option, the results are varying, please guide further as to which method should be used or where I may be wrong.

Thank You

[Jared Greathouse]

Itesh New Please read the FAQ on how to ask a question.

[Clyde Schechter]

@ltesh New Also note that your question is unrelated to the topic of this thread. Please, when you repost, do start a new thread.