Dear Stata Users

1. I need to calculate a rolling correlation for variable eret with marketreturn vw for the past 60 months, with a minimum of 36 months and a maximum of 60 months.
I have created a counter per seriesid (I have 50000 series) to see which series had a length of atleast 35 and if it is greater than 60 it is set to 60.

Code:
by seriesid: generate counter1 = _n
replace counter = 60 if counter1 > 60
The command I have tried is the following:
Code:
rolling correlation=r(rho), window(counter1): corr seriesid marketreturnvw if counter1 > 35
It obviously gives the error that the window has an invalid number although the variable counter1 stores a numerical value. Rolling correlation does not give the options to include a minimum or maximum and neither does rangestat, how could I solve my problem? A representative example of my dataset can be found here, I did not want to post it as long as this however it gives the three types of series I can encounter. For serie 1 (398161) it should only calculate the last 4 observations (36-39), for series 2 (398162) from 36 up and including till 60 and for series 3 (398163) it should not calculate anything.
Code:
seriesid    datem    eret    marketreturnvw    counter1
398161    22903    .0515464    .0533712    1
398161    22904    0    .0196333    2
398161    22905    .0098039    -.0110006    3
398161    22906    0    .0259953    4
398161    22907    0    .0602705    5
398161    22908    -.0194175    .0238937    6
398161    22909    .020202    -.0176534    7
398161    22910    0    -.0052787    8
398161    22911    0    .0451855    9
398161    22912    0    .0235492    10
398161    22913    0    .0240288    11
398161    22914    -.0158416    .0193326    12
398161    22915    .0205339    .0279241    13
398161    22916    0    .0096572    14
398161    22917    0    .0192729    15
398161    22918    0    -.003256    16
398161    22919    -.0080483    -.0110087    17
398161    22920    0    .0266223    18
398161    22921    .0248447    -.0372576    19
398161    22922    .0040404    .0012145    20
398161    22923    .0100604    .0004184    21
398161    22924    .001992    -.0181646    22
398161    22925    -.0318091    .0065122    23
398161    22926    0    -.0422091    24
398161    22927    -.0251572    -.0183423    25
398161    22928    .0043011    .0250081    26
398161    22929    0    .0185326    27
398161    22930    .0449679    .0338697    28
398161    22931    -.0471311    -.0120151    29
398161    22932    -.0107527    .0038153    30
398161    22933    .0177778    .0344987    31
398161    22934    .0065502    .001603    32
398161    22935    0    .0098711    33
398161    22936    .0520607    .0049338    34
398161    22937    .0103093    .0169208    35
398161    22938    -.0306122    .0106069    36
398161    22939    4.55e-08    .0016865    37
398161    22940    0    -.0561744    38
398161    22941    0    -.0091954    39
398162    22663    .0512821    .0581949    1
398162    22664    -.0487805    .0142794    2
398162    22665    .025641    .0160483    3
398162    22666    -.05    -.0238813    4
398162    22667    .0526316    -.0128762    5
398162    22668    -.05    .0112311    6
398162    22669    .027027    .0190756    7
398162    22670    0    .0052358    8
398162    22671    -.0578947    -.0266858    9
398162    22672    .0446927    .0181575    10
398162    22673    0    .0375406    11
398162    22674    0    -.0040143    12
398162    22675    .0123627    -.008784    13
398162    22676    .0312076    .0354086    14
398162    22677    .0263158    .008895    15
398162    22678    -.0102564    -.0028574    16
398162    22679    -.0265544    -.0028214    17
398162    22680    0    .0100834    18
398162    22681    .1045796    .0181616    19
398162    22682    -.0111386    -.0170125    20
398162    22683    .0438048    .0118075    21
398162    22686    -.0052539    -.0008513    22
398162    22687    .0673077    -.0042366    23
398162    22688    -.036036    -.0085963    24
398162    22689    .0373832    -.024691    25
398162    22690    0    -.035038    26
398162    22691    -.045045    -.023252    27
398162    22692    -.009434    -.0223915    28
398162    22693    -.0097561    .0308523    29
398162    22694    0    .0089379    30
398162    22695    -.0098522    -.0055645    31
398162    22696    0    .0391853    32
398162    22697    -.0049751    -.0167476    33
398162    22698    -.05    -.0209592    34
398162    22699    .0810811    -.017267    35
398162    22700    -.015    .0720364    36
398162    22701    0    .0227344    37
398162    22702    -.0228426    .0001329    38
398162    22703    -.012987    .000615    39
398162    22704    -.0098684    .0281079    40
398162    22705    .0273038    .0218483    41
398162    22706    0    .0073688    42
398162    22707    .0019934    -.0026331    43
398162    22708    -.0019894    .0031642    44
398162    22709    0    -.0025553    45
398162    22710    .0631229    .0017775    46
398162    22711    4.36e-08    .019504    47
398162    22712    -.025641    -.0123618    48
398162    22713    0    .0036592    49
398162    22714    -.0302632    .0248125    50
398162    22715    .0312076    -.0172142    51
398162    22716    -.0263158    .0117173    52
398162    22717    .0569444    .0230132    53
398162    22718    .0249671    -.0217525    54
398162    22719    .0108974    -.0016715    55
398162    22720    .0044388    -.0193851    56
398162    22721    0    -.0842723    57
398162    22722    0    -.0287036    58
398162    22723    .0259067    -.1253573    59
398162    22724    0    .065193    60
398162    22725    0    .0464678    60
398163    22802    0    .0054234    1
398163    22803    0    .0285169    2
398163    22804    0    -.0149056    3
398163    22805    0    -.0047905    4
398163    22806    0    -.0316357    5
398163    22807    .0273973    .0171092    6
398163    22808    0    .0114972    7
398163    22809    0    -.0163915    8
398163    22810    0    .0547485    9
398163    22811    0    .0985985    10
398163    22812    0    .046222    11
398163    22813    .0273972    .0124376    12
398163    22814    0    .0139258    13

Thank you,
Bart


Tags: None