Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Generate mean based on condition, where condition is based on variable

    Hello dear Statalist-experts,

    I have a daily panel-dataset in long format, containing, among other things, the date of the observation (Date), a firm identifier (DSCD), a firm's market value on that date (MV) and the month of its fiscal year end (FYE_month).
    What I am trying to do is for each observation (firm-day), get the mean market value for the month that lies 7 months in the future from the end of the fiscal year this obsevation lies in.
    That is, if the observation is from Date 05.01.2012, the firm's fiscal year ends in December 2012, I want to generate a new variable MV_future containing the average MV for all observations of that firm that lie in July 2013.
    What I've come up with so far is to generate a new variable measurement_month that is the FYE_month + 7. Then I wanted to use egen mean(), but I don't know how to formulate the condion in a way that I can reference the current observation's measurement_month variable, and then only include those observations from the specified month in the mean calculation.
    The idea was something like

    Code:
    by DSCD, sort: egen MV_future = mean(cond(MONTH == measurement_month[1], MV, .))
    However, this always takes the first observation's value of measurement month, not the current one's. If I leave out the [1], however, I feel like the formula compares both values for the current observation, finds that they're not equal, and generates only missing values.

    The second idea was to xtset DSCD Date my data and use time series operators. But a) that diesn't give me monthly averages but daily values, and the panel has some missing values, so some days may be missing.
    So, what would be a better way to approach this problem?
    Thank you very much in advance!

    Data example:


    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input str6 DSCD float(Date YEAR MONTH FYE_month measurement_month) double MV
    "13254K" 18991 2011 623   .   .  44.63
    "13254K" 18994 2012 624 635 642  44.63
    "13254K" 18995 2012 624 635 642  45.06
    "13254K" 18996 2012 624 635 642  43.84
    "13254K" 18997 2012 624 635 642  43.75
    "13254K" 18998 2012 624 635 642  43.75
    "13254K" 19001 2012 624 635 642  43.75
    "13254K" 19002 2012 624 635 642  46.29
    "13254K" 19003 2012 624 635 642  44.28
    "13254K" 19004 2012 624 635 642  44.28
    "13254K" 19005 2012 624 635 642  44.28
    "13254K" 19008 2012 624 635 642  43.84
    "13254K" 19009 2012 624 635 642  43.75
    "13254K" 19010 2012 624 635 642  44.63
    "13254K" 19011 2012 624 635 642  44.63
    "13254K" 19012 2012 624 635 642  44.01
    "13254K" 19015 2012 624 635 642  43.84
    "13254K" 19016 2012 624 635 642  43.75
    "13254K" 19017 2012 624 635 642  44.71
    "13254K" 19018 2012 624 635 642  43.93
    "13254K" 19019 2012 624 635 642  44.01
    "13254K" 19022 2012 624 635 642  43.75
    "13254K" 19023 2012 624 635 642  43.75
    "13254K" 19024 2012 625 635 642  44.28
    "13254K" 19025 2012 625 635 642   45.5
    "13254K" 19026 2012 625 635 642   45.5
    "13254K" 19029 2012 625 635 642  45.85
    "13254K" 19030 2012 625 635 642  46.29
    "13254K" 19031 2012 625 635 642  48.04
    "13254K" 19032 2012 625 635 642  48.56
    "13254K" 19033 2012 625 635 642  48.13
    "13254K" 19036 2012 625 635 642  47.25
    "13254K" 19037 2012 625 635 642  48.56
    "13254K" 19038 2012 625 635 642  48.56
    "13254K" 19039 2012 625 635 642  48.13
    "13254K" 19040 2012 625 635 642  48.21
    "13254K" 19043 2012 625 635 642  48.56
    "13254K" 19044 2012 625 635 642  48.39
    "13254K" 19045 2012 625 635 642  47.43
    "13254K" 19046 2012 625 635 642  47.43
    "13254K" 19047 2012 625 635 642   47.6
    "13254K" 19050 2012 625 635 642   47.6
    "13254K" 19051 2012 625 635 642  48.21
    "13254K" 19052 2012 625 635 642  49.18
    "13254K" 19053 2012 626 635 642  49.79
    "13254K" 19054 2012 626 635 642  49.88
    "13254K" 19057 2012 626 635 642  49.79
    "13254K" 19058 2012 626 635 642  49.88
    "13254K" 19059 2012 626 635 642  48.39
    "13254K" 19060 2012 626 635 642  48.48
    "13254K" 19061 2012 626 635 642  48.48
    "13254K" 19064 2012 626 635 642   48.3
    "13254K" 19065 2012 626 635 642  49.79
    "13254K" 19066 2012 626 635 642  47.08
    "13254K" 19067 2012 626 635 642  48.21
    "13254K" 19068 2012 626 635 642  46.38
    "13254K" 19071 2012 626 635 642   45.5
    "13254K" 19072 2012 626 635 642  46.46
    "13254K" 19073 2012 626 635 642  47.34
    "13254K" 19074 2012 626 635 642  47.34
    "13254K" 19075 2012 626 635 642  47.86
    "13254K" 19078 2012 626 635 642  48.48
    "13254K" 19079 2012 626 635 642  48.13
    "13254K" 19080 2012 626 635 642  48.04
    "13254K" 19081 2012 626 635 642  48.13
    "13254K" 19082 2012 626 635 642  48.21
    "13254K" 19085 2012 627 635 642  47.34
    "13254K" 19086 2012 627 635 642  47.08
    "13254K" 19087 2012 627 635 642  47.16
    "13254K" 19088 2012 627 635 642  47.69
    "13254K" 19089 2012 627 635 642  47.69
    "13254K" 19092 2012 627 635 642  47.69
    "13254K" 19093 2012 627 635 642  48.21
    "13254K" 19094 2012 627 635 642  48.21
    "13254K" 19095 2012 627 635 642  49.09
    "13254K" 19096 2012 627 635 642  49.09
    "13254K" 19099 2012 627 635 642  49.09
    "13254K" 19100 2012 627 635 642  49.09
    "13254K" 19101 2012 627 635 642  50.05
    "13254K" 19102 2012 627 635 642  51.01
    "13254K" 19103 2012 627 635 642  53.99
    "13254K" 19106 2012 627 635 642   56.7
    "13254K" 19107 2012 627 635 642  56.88
    "13254K" 19108 2012 627 635 642  57.93
    "13254K" 19109 2012 627 635 642  57.93
    "13254K" 19110 2012 627 635 642   59.5
    "13254K" 19113 2012 627 635 642  61.34
    "13254K" 19114 2012 628 635 642  61.34
    "13254K" 19115 2012 628 635 642  62.21
    "13254K" 19116 2012 628 635 642  64.14
    "13254K" 19117 2012 628 635 642   65.8
    "13254K" 19120 2012 628 635 642  64.23
    "13254K" 19121 2012 628 635 642  66.06
    "13254K" 19122 2012 628 635 642  66.06
    "13254K" 19123 2012 628 635 642  65.54
    "13254K" 19124 2012 628 635 642  67.29
    "13254K" 19127 2012 628 635 642  66.33
    "13254K" 19128 2012 628 635 642  65.54
    "13254K" 19129 2012 628 635 642  65.54
    "13254K" 19130 2012 628 635 642  64.75
    "13254K" 19131 2012 628 635 642  64.05
    "13254K" 19134 2012 628 635 642  64.23
    "13254K" 19135 2012 628 635 642  61.78
    "13254K" 19136 2012 628 635 642  61.25
    "13254K" 19137 2012 628 635 642  61.25
    "13254K" 19138 2012 628 635 642  62.48
    "13254K" 19141 2012 628 635 642  59.06
    "13254K" 19142 2012 628 635 642  58.28
    "13254K" 19143 2012 628 635 642  56.96
    "13254K" 19144 2012 628 635 642  57.49
    "13254K" 19145 2012 629 635 642  53.73
    "13254K" 19148 2012 629 635 642  53.73
    "13254K" 19149 2012 629 635 642  56.18
    "13254K" 19150 2012 629 635 642  55.13
    "13254K" 19151 2012 629 635 642  56.44
    "13254K" 19152 2012 629 635 642  56.44
    "13254K" 19155 2012 629 635 642  55.13
    "13254K" 19156 2012 629 635 642  55.91
    "13254K" 19157 2012 629 635 642     56
    "13254K" 19158 2012 629 635 642  56.26
    "13254K" 19159 2012 629 635 642   56.7
    "13254K" 19162 2012 629 635 642  56.96
    "13254K" 19163 2012 629 635 642  57.58
    "13254K" 19164 2012 629 635 642  60.81
    "13254K" 19165 2012 629 635 642  60.81
    "13254K" 19166 2012 629 635 642  57.31
    "13254K" 19169 2012 629 635 642  61.08
    "13254K" 19170 2012 629 635 642  61.16
    "13254K" 19171 2012 629 635 642   60.2
    "13254K" 19172 2012 629 635 642  60.29
    "13254K" 19173 2012 629 635 642  60.55
    "13254K" 19176 2012 630 635 642   59.5
    "13254K" 19177 2012 630 635 642  58.63
    "13254K" 19178 2012 630 635 642  58.63
    "13254K" 19179 2012 630 635 642  58.63
    "13254K" 19180 2012 630 635 642  60.81
    "13254K" 19183 2012 630 635 642  60.81
    "13254K" 19184 2012 630 635 642  60.81
    "13254K" 19185 2012 630 635 642  58.71
    "13254K" 19186 2012 630 635 642  58.71
    "13254K" 19187 2012 630 635 642   58.8
    "13254K" 19190 2012 630 635 642  58.01
    "13254K" 19191 2012 630 635 642  57.75
    "13254K" 19192 2012 630 635 642  57.49
    "13254K" 19193 2012 630 635 642  56.96
    "13254K" 19194 2012 630 635 642  57.75
    "13254K" 19197 2012 630 635 642  56.09
    "13254K" 19198 2012 630 635 642  58.01
    "13254K" 19199 2012 630 635 642   56.7
    "13254K" 19200 2012 630 635 642  55.56
    "13254K" 19201 2012 630 635 642  56.88
    "13254K" 19204 2012 630 635 642  59.33
    "13254K" 19205 2012 630 635 642  59.41
    "13254K" 19206 2012 631 635 642   59.5
    "13254K" 19207 2012 631 635 642  61.34
    "13254K" 19208 2012 631 635 642  60.64
    "13254K" 19211 2012 631 635 642  62.13
    "13254K" 19212 2012 631 635 642  62.13
    "13254K" 19213 2012 631 635 642  59.15
    "13254K" 19214 2012 631 635 642  61.25
    "13254K" 19215 2012 631 635 642  61.43
    "13254K" 19218 2012 631 635 642  61.43
    "13254K" 19219 2012 631 635 642  61.43
    "13254K" 19220 2012 631 635 642  61.43
    "13254K" 19221 2012 631 635 642  63.61
    "13254K" 19222 2012 631 635 642     63
    "13254K" 19225 2012 631 635 642     63
    "13254K" 19226 2012 631 635 642     63
    "13254K" 19227 2012 631 635 642  62.13
    "13254K" 19228 2012 631 635 642  62.56
    "13254K" 19229 2012 631 635 642  61.43
    "13254K" 19232 2012 631 635 642     63
    "13254K" 19233 2012 631 635 642  63.61
    "13254K" 19234 2012 631 635 642  62.56
    "13254K" 19235 2012 631 635 642   61.6
    "13254K" 19236 2012 631 635 642  61.86
    "13254K" 19239 2012 632 635 642  62.21
    "13254K" 19240 2012 632 635 642  62.13
    "13254K" 19241 2012 632 635 642  59.59
    "13254K" 19242 2012 632 635 642  62.13
    "13254K" 19243 2012 632 635 642  62.04
    "13254K" 19246 2012 632 635 642  61.43
    "13254K" 19247 2012 632 635 642  58.45
    "13254K" 19248 2012 632 635 642  54.69
    "13254K" 19249 2012 632 635 642  57.14
    "13254K" 19250 2012 632 635 642  58.63
    "13254K" 19253 2012 632 635 642  59.68
    "13254K" 19254 2012 632 635 642  59.76
    "13254K" 19255 2012 632 635 642   60.2
    "13254K" 19256 2012 632 635 642   60.2
    "13254K" 19257 2012 632 635 642   60.2
    "13254K" 19260 2012 632 635 642   60.2
    "13254K" 19261 2012 632 635 642  57.31
    "13254K" 19262 2012 632 635 642  59.94
    "13254K" 19263 2012 632 635 642   60.2
    "13254K" 19264 2012 632 635 642  57.31
    "13254K" 19267 2012 633 635 642  57.31
    "13254K" 19268 2012 633 635 642  58.36
    "13254K" 19269 2012 633 635 642  57.75
    "13254K" 19270 2012 633 635 642  58.54
    "13254K" 19271 2012 633 635 642  60.55
    "13254K" 19274 2012 633 635 642  57.93
    "13254K" 19275 2012 633 635 642  57.75
    "13254K" 19276 2012 633 635 642  57.75
    "13254K" 19277 2012 633 635 642   59.5
    "13254K" 19278 2012 633 635 642  60.64
    "13254K" 19281 2012 633 635 642  61.25
    "13254K" 19282 2012 633 635 642  60.55
    "13254K" 19283 2012 633 635 642  60.38
    "13254K" 19284 2012 633 635 642   61.6
    "13254K" 19285 2012 633 635 642  61.34
    "13254K" 19288 2012 633 635 642     63
    "13254K" 19289 2012 633 635 642  65.54
    "13254K" 19290 2012 633 635 642  65.54
    "13254K" 19291 2012 633 635 642  65.54
    "13254K" 19292 2012 633 635 642     63
    "13254K" 19295 2012 633 635 642     63
    "13254K" 19296 2012 633 635 642     63
    "13254K" 19297 2012 633 635 642   64.4
    "13254K" 19298 2012 634 635 642     63
    "13254K" 19299 2012 634 635 642     63
    "13254K" 19302 2012 634 635 642     63
    "13254K" 19303 2012 634 635 642  63.79
    "13254K" 19304 2012 634 635 642  63.79
    "13254K" 19305 2012 634 635 642  63.79
    "13254K" 19306 2012 634 635 642  64.84
    "13254K" 19309 2012 634 635 642  64.84
    "13254K" 19310 2012 634 635 642   63.7
    "13254K" 19311 2012 634 635 642   63.7
    "13254K" 19312 2012 634 635 642   63.7
    "13254K" 19313 2012 634 635 642  62.39
    "13254K" 19316 2012 634 635 642  66.94
    "13254K" 19317 2012 634 635 642  66.94
    "13254K" 19318 2012 634 635 642  66.94
    "13254K" 19319 2012 634 635 642     63
    "13254K" 19320 2012 634 635 642     63
    "13254K" 19323 2012 634 635 642     63
    "13254K" 19324 2012 634 635 642  63.26
    "13254K" 19325 2012 634 635 642  63.26
    "13254K" 19326 2012 634 635 642  63.26
    "13254K" 19327 2012 634 635 642  63.26
    "13254K" 19330 2012 635 635 642  63.26
    "13254K" 19331 2012 635 635 642  63.53
    "13254K" 19332 2012 635 635 642  63.53
    "13254K" 19333 2012 635 635 642  62.56
    "13254K" 19334 2012 635 635 642  63.26
    "13254K" 19337 2012 635 635 642  63.35
    "13254K" 19338 2012 635 635 642  63.35
    "13254K" 19339 2012 635 635 642  63.18
    "13254K" 19340 2012 635 635 642  63.18
    "13254K" 19341 2012 635 635 642  63.18
    "13254K" 19344 2012 635 635 642  61.43
    "13254K" 19345 2012 635 635 642  61.43
    "13254K" 19346 2012 635 635 642  61.43
    "13254K" 19347 2012 635 635 642  63.26
    "13254K" 19348 2012 635 635 642     63
    "13254K" 19351 2012 635 635 642     63
    "13254K" 19352 2012 635 635 642     63
    "13254K" 19353 2012 635 635 642     63
    "13254K" 19354 2012 635 635 642     63
    "13254K" 19355 2012 635 635 642     63
    "13254K" 19358 2012 635 635 642     63
    "13254K" 19359 2013 636 647 654     63
    "13254K" 19360 2013 636 647 654     63
    "13254K" 19361 2013 636 647 654     63
    "13254K" 19362 2013 636 647 654   62.3
    "13254K" 19365 2013 636 647 654  63.79
    "13254K" 19366 2013 636 647 654  63.79
    "13254K" 19367 2013 636 647 654  64.58
    "13254K" 19368 2013 636 647 654  64.58
    "13254K" 19369 2013 636 647 654  64.58
    "13254K" 19372 2013 636 647 654  64.58
    "13254K" 19373 2013 636 647 654  64.58
    "13254K" 19374 2013 636 647 654  64.58
    "13254K" 19375 2013 636 647 654  64.58
    "13254K" 19376 2013 636 647 654  64.58
    "13254K" 19379 2013 636 647 654  64.31
    "13254K" 19380 2013 636 647 654  64.31
    "13254K" 19381 2013 636 647 654  65.19
    "13254K" 19382 2013 636 647 654  62.74
    "13254K" 19383 2013 636 647 654  64.93
    "13254K" 19386 2013 636 647 654  62.65
    "13254K" 19387 2013 636 647 654  62.65
    "13254K" 19388 2013 636 647 654   62.3
    "13254K" 19389 2013 636 647 654   62.3
    "13254K" 19390 2013 637 647 654   62.3
    "13254K" 19393 2013 637 647 654   62.3
    "13254K" 19394 2013 637 647 654   62.3
    "13254K" 19395 2013 637 647 654  65.63
    "13254K" 19396 2013 637 647 654  65.63
    "13254K" 19397 2013 637 647 654  65.63
    "13254K" 19400 2013 637 647 654  71.75
    "13254K" 19401 2013 637 647 654  71.75
    "13254K" 19402 2013 637 647 654  71.75
    "13254K" 19403 2013 637 647 654  71.75
    "13254K" 19404 2013 637 647 654     70
    "13254K" 19407 2013 637 647 654  71.75
    "13254K" 19408 2013 637 647 654     70
    "13254K" 19409 2013 637 647 654  72.19
    "13254K" 19410 2013 637 647 654  72.19
    "13254K" 19411 2013 637 647 654  72.19
    "13254K" 19414 2013 637 647 654  67.81
    "13254K" 19415 2013 637 647 654  65.89
    "13254K" 19416 2013 637 647 654  69.39
    "13254K" 19417 2013 637 647 654  69.74
    "13254K" 19418 2013 638 647 654  70.26
    "13254K" 19421 2013 638 647 654  72.54
    "13254K" 19422 2013 638 647 654  74.11
    "13254K" 19423 2013 638 647 654  73.94
    "13254K" 19424 2013 638 647 654  73.94
    "13254K" 19425 2013 638 647 654  73.94
    "13254K" 19428 2013 638 647 654  69.83
    "13254K" 19429 2013 638 647 654  69.83
    "13254K" 19430 2013 638 647 654  69.83
    "13254K" 19431 2013 638 647 654  71.75
    "13254K" 19432 2013 638 647 654  67.99
    "13254K" 19435 2013 638 647 654  67.99
    "13254K" 19436 2013 638 647 654  67.99
    "13254K" 19437 2013 638 647 654  67.99
    "13254K" 19438 2013 638 647 654  67.99
    "13254K" 19439 2013 638 647 654  67.99
    "13254K" 19442 2013 638 647 654  67.99
    "13254K" 19443 2013 638 647 654  67.99
    "13254K" 19444 2013 638 647 654  67.99
    "13254K" 19445 2013 638 647 654  67.99
    "13254K" 19446 2013 638 647 654  67.99
    "13254K" 19449 2013 639 647 654  67.99
    "13254K" 19450 2013 639 647 654  62.13
    "13254K" 19451 2013 639 647 654  62.13
    "13254K" 19452 2013 639 647 654  55.91
    "13254K" 19453 2013 639 647 654  57.14
    "13254K" 19456 2013 639 647 654  57.14
    "13254K" 19457 2013 639 647 654  61.25
    "13254K" 19458 2013 639 647 654  61.25
    "13254K" 19459 2013 639 647 654  61.25
    "13254K" 19460 2013 639 647 654  62.13
    "13254K" 19463 2013 639 647 654  61.86
    "13254K" 19464 2013 639 647 654  63.18
    "13254K" 19465 2013 639 647 654  62.13
    "13254K" 19466 2013 639 647 654  62.04
    "13254K" 19467 2013 639 647 654  61.86
    "13254K" 19470 2013 639 647 654  61.86
    "13254K" 19471 2013 639 647 654  61.25
    "13254K" 19472 2013 639 647 654  63.61
    "13254K" 19473 2013 639 647 654  63.53
    "13254K" 19474 2013 639 647 654  62.13
    "13254K" 19477 2013 639 647 654  62.04
    "13254K" 19478 2013 639 647 654  62.04
    "13254K" 19479 2013 640 647 654  62.04
    "13254K" 19480 2013 640 647 654  62.04
    "13254K" 19481 2013 640 647 654  62.04
    "13254K" 19484 2013 640 647 654  62.04
    "13254K" 19485 2013 640 647 654  62.04
    "13254K" 19486 2013 640 647 654  63.44
    "13254K" 19487 2013 640 647 654     63
    "13254K" 19488 2013 640 647 654     63
    "13254K" 19491 2013 640 647 654     63
    "13254K" 19492 2013 640 647 654     63
    "13254K" 19493 2013 640 647 654     63
    "13254K" 19494 2013 640 647 654  66.94
    "13254K" 19495 2013 640 647 654  72.63
    "13254K" 19498 2013 640 647 654  72.63
    "13254K" 19499 2013 640 647 654  72.63
    "13254K" 19500 2013 640 647 654  75.78
    "13254K" 19501 2013 640 647 654  75.78
    "13254K" 19502 2013 640 647 654  68.25
    "13254K" 19505 2013 640 647 654  68.25
    "13254K" 19506 2013 640 647 654   66.5
    "13254K" 19507 2013 640 647 654   66.5
    "13254K" 19508 2013 640 647 654  70.88
    "13254K" 19509 2013 640 647 654  72.63
    "13254K" 19512 2013 641 647 654   73.5
    "13254K" 19513 2013 641 647 654   73.5
    "13254K" 19514 2013 641 647 654  70.61
    "13254K" 19515 2013 641 647 654  72.63
    "13254K" 19516 2013 641 647 654  72.63
    "13254K" 19519 2013 641 647 654  72.63
    "13254K" 19520 2013 641 647 654  72.63
    "13254K" 19521 2013 641 647 654  69.13
    "13254K" 19522 2013 641 647 654  69.13
    "13254K" 19523 2013 641 647 654  70.88
    "13254K" 19526 2013 641 647 654     70
    "13254K" 19527 2013 641 647 654     70
    "13254K" 19528 2013 641 647 654     70
    "13254K" 19529 2013 641 647 654     70
    "13254K" 19530 2013 641 647 654  65.63
    "13254K" 19533 2013 641 647 654  65.63
    "13254K" 19534 2013 641 647 654  65.63
    "13254K" 19535 2013 641 647 654  69.13
    "13254K" 19536 2013 641 647 654  69.13
    "13254K" 19537 2013 641 647 654  74.38
    "13254K" 19540 2013 642 647 654  75.25
    "13254K" 19541 2013 642 647 654  69.65
    "13254K" 19542 2013 642 647 654  71.75
    "13254K" 19543 2013 642 647 654  68.25
    "13254K" 19544 2013 642 647 654  68.25
    "13254K" 19547 2013 642 647 654  69.13
    "13254K" 19548 2013 642 647 654  74.38
    "13254K" 19549 2013 642 647 654  74.38
    "13254K" 19550 2013 642 647 654  74.38
    "13254K" 19551 2013 642 647 654  73.59
    "13254K" 19554 2013 642 647 654  73.59
    "13254K" 19555 2013 642 647 654  75.69
    "13254K" 19556 2013 642 647 654  75.69
    "13254K" 19557 2013 642 647 654     77
    "13254K" 19558 2013 642 647 654  73.94
    "13254K" 19561 2013 642 647 654  74.38
    "13254K" 19562 2013 642 647 654  78.58
    "13254K" 19563 2013 642 647 654  74.38
    "13254K" 19564 2013 642 647 654  74.81
    "13254K" 19565 2013 642 647 654  78.31
    "13254K" 19568 2013 642 647 654  78.31
    "13254K" 19569 2013 642 647 654  74.38
    "13254K" 19570 2013 642 647 654  74.99
    "13254K" 19571 2013 643 647 654  77.18
    "13254K" 19572 2013 643 647 654  76.13
    "13254K" 19575 2013 643 647 654  76.39
    "13254K" 19576 2013 643 647 654  74.38
    "13254K" 19577 2013 643 647 654  75.25
    "13254K" 19578 2013 643 647 654  74.55
    "13254K" 19579 2013 643 647 654  75.25
    "13254K" 19582 2013 643 647 654  75.25
    "13254K" 19583 2013 643 647 654  75.25
    "13254K" 19584 2013 643 647 654  75.25
    "13254K" 19585 2013 643 647 654  75.25
    "13254K" 19586 2013 643 647 654  75.25
    "13254K" 19589 2013 643 647 654  75.25
    "13254K" 19590 2013 643 647 654  72.71
    "13254K" 19591 2013 643 647 654  72.71
    "13254K" 19592 2013 643 647 654  71.93
    "13254K" 19593 2013 643 647 654  71.31
    "13254K" 19596 2013 643 647 654  70.88
    "13254K" 19597 2013 643 647 654     70
    "13254K" 19598 2013 643 647 654     70
    "13254K" 19599 2013 643 647 654  69.13
    "13254K" 19600 2013 643 647 654     70
    "13254K" 19603 2013 644 647 654  73.94
    "13254K" 19604 2013 644 647 654  73.94
    "13254K" 19605 2013 644 647 654  70.44
    "13254K" 19606 2013 644 647 654  71.75
    "13254K" 19607 2013 644 647 654  74.29
    "13254K" 19610 2013 644 647 654   73.5
    "13254K" 19611 2013 644 647 654   73.5
    "13254K" 19612 2013 644 647 654   73.5
    "13254K" 19613 2013 644 647 654   73.5
    "13254K" 19614 2013 644 647 654   73.5
    "13254K" 19617 2013 644 647 654   73.5
    "13254K" 19618 2013 644 647 654   73.5
    "13254K" 19619 2013 644 647 654     70
    "13254K" 19620 2013 644 647 654  72.63
    "13254K" 19621 2013 644 647 654  72.63
    "13254K" 19624 2013 644 647 654  72.63
    "13254K" 19625 2013 644 647 654  72.63
    "13254K" 19626 2013 644 647 654  72.63
    "13254K" 19627 2013 644 647 654  69.91
    "13254K" 19628 2013 644 647 654  72.54
    "13254K" 19631 2013 644 647 654  72.54
    "13254K" 19632 2013 645 647 654  72.54
    "13254K" 19633 2013 645 647 654  74.38
    "13254K" 19634 2013 645 647 654  71.75
    "13254K" 19635 2013 645 647 654  71.75
    "13254K" 19638 2013 645 647 654  70.88
    "13254K" 19639 2013 645 647 654  70.88
    "13254K" 19640 2013 645 647 654  68.25
    "13254K" 19641 2013 645 647 654  68.25
    "13254K" 19642 2013 645 647 654     70
    "13254K" 19645 2013 645 647 654  71.75
    "13254K" 19646 2013 645 647 654  69.13
    "13254K" 19647 2013 645 647 654  67.81
    "13254K" 19648 2013 645 647 654  67.81
    "13254K" 19649 2013 645 647 654  67.81
    "13254K" 19652 2013 645 647 654  68.25
    "13254K" 19653 2013 645 647 654  68.95
    "13254K" 19654 2013 645 647 654  68.95
    "13254K" 19655 2013 645 647 654  65.71
    "13254K" 19656 2013 645 647 654  68.95
    "13254K" 19659 2013 645 647 654  68.95
    "13254K" 19660 2013 645 647 654  68.95
    "13254K" 19661 2013 645 647 654  68.95
    "13254K" 19662 2013 645 647 654  69.56
    "13254K" 19663 2013 646 647 654   66.5
    "13254K" 19666 2013 646 647 654  68.95
    "13254K" 19667 2013 646 647 654     70
    "13254K" 19668 2013 646 647 654     70
    "13254K" 19669 2013 646 647 654     70
    "13254K" 19670 2013 646 647 654     70
    "13254K" 19673 2013 646 647 654  71.93
    "13254K" 19674 2013 646 647 654   76.3
    "13254K" 19675 2013 646 647 654  77.61
    "13254K" 19676 2013 646 647 654  78.58
    "13254K" 19677 2013 646 647 654  91.87
    "13254K" 19680 2013 646 647 654  92.75
    "13254K" 19681 2013 646 647 654  92.75
    "13254K" 19682 2013 646 647 654  93.63
    "13254K" 19683 2013 646 647 654  93.63
    "13254K" 19684 2013 646 647 654  91.87
    "13254K" 19687 2013 646 647 654  88.37
    "13254K" 19688 2013 646 647 654  88.37
    "13254K" 19689 2013 646 647 654  88.37
    "13254K" 19690 2013 646 647 654  88.37
    "13254K" 19691 2013 646 647 654   87.5
    "13254K" 19694 2013 647 647 654   87.5
    "13254K" 19695 2013 647 647 654  87.41
    "13254K" 19696 2013 647 647 654  91.87
    "13254K" 19697 2013 647 647 654   87.5
    "13254K" 19698 2013 647 647 654  86.63
    "13254K" 19701 2013 647 647 654  86.63
    "13254K" 19702 2013 647 647 654  86.63
    "13254K" 19703 2013 647 647 654  86.19
    "13254K" 19704 2013 647 647 654  84.18
    "13254K" 19705 2013 647 647 654  80.06
    "13254K" 19708 2013 647 647 654  80.06
    "13254K" 19709 2013 647 647 654  80.68
    "13254K" 19710 2013 647 647 654  81.03
    "13254K" 19711 2013 647 647 654  81.38
    "13254K" 19712 2013 647 647 654  82.69
    "13254K" 19715 2013 647 647 654  82.78
    "13254K" 19716 2013 647 647 654  82.78
    "13254K" 19717 2013 647 647 654  82.78
    "13254K" 19718 2013 647 647 654  82.78
    "13254K" 19719 2013 647 647 654   80.5
    "13254K" 19722 2013 647 647 654  80.68
    "13254K" 19723 2013 647 647 654  80.68
    "13254K" 19724 2014 648 659 666  80.68
    "13254K" 19725 2014 648 659 666  89.25
    "13254K" 19726 2014 648 659 666  96.25
    "13254K" 19729 2014 648 659 666  96.25
    "13254K" 19730 2014 648 659 666 103.25
    "13254K" 19731 2014 648 659 666 102.37
    "13254K" 19732 2014 648 659 666 100.63
    "13254K" 19733 2014 648 659 666 105.87
    "13254K" 19736 2014 648 659 666  97.13
    "13254K" 19737 2014 648 659 666 100.63
    "13254K" 19738 2014 648 659 666 100.63
    "13254K" 19739 2014 648 659 666 100.63
    "13254K" 19740 2014 648 659 666    105
    "13254K" 19743 2014 648 659 666 100.63
    "13254K" 19744 2014 648 659 666  98.87
    "13254K" 19745 2014 648 659 666 102.37
    "13254K" 19746 2014 648 659 666 102.37
    "13254K" 19747 2014 648 659 666 103.25
    "13254K" 19750 2014 648 659 666    105
    "13254K" 19751 2014 648 659 666 105.87
    "13254K" 19752 2014 648 659 666  101.5
    "13254K" 19753 2014 648 659 666 104.13
    "13254K" 19754 2014 648 659 666 104.13
    "13254K" 19757 2014 649 659 666 104.13
    "13254K" 19758 2014 649 659 666 103.25
    "13254K" 19759 2014 649 659 666  108.5
    "13254K" 19760 2014 649 659 666  108.5
    "13254K" 19761 2014 649 659 666 103.25
    "13254K" 19764 2014 649 659 666  101.5
    "13254K" 19765 2014 649 659 666  101.5
    "13254K" 19766 2014 649 659 666    105
    "13254K" 19767 2014 649 659 666 103.25
    "13254K" 19768 2014 649 659 666 104.13
    "13254K" 19771 2014 649 659 666 105.87
    "13254K" 19772 2014 649 659 666 114.63
    "13254K" 19773 2014 649 659 666 109.38
    "13254K" 19774 2014 649 659 666 111.13
    "13254K" 19775 2014 649 659 666 111.13
    "13254K" 19778 2014 649 659 666 107.63
    "13254K" 19779 2014 649 659 666 105.87
    "13254K" 19780 2014 649 659 666 109.38
    "13254K" 19781 2014 649 659 666 109.38
    "13254K" 19782 2014 649 659 666    105
    "13254K" 19785 2014 650 659 666    105
    "13254K" 19786 2014 650 659 666 109.38
    "13254K" 19787 2014 650 659 666 110.25
    "13254K" 19788 2014 650 659 666 113.75
    "13254K" 19789 2014 650 659 666    112
    "13254K" 19792 2014 650 659 666    112
    "13254K" 19793 2014 650 659 666    112
    "13254K" 19794 2014 650 659 666  108.5
    "13254K" 19795 2014 650 659 666 109.38
    "13254K" 19796 2014 650 659 666 112.88
    "13254K" 19799 2014 650 659 666 107.63
    "13254K" 19800 2014 650 659 666    112
    "13254K" 19801 2014 650 659 666 107.63
    "13254K" 19802 2014 650 659 666  108.5
    "13254K" 19803 2014 650 659 666    105
    "13254K" 19806 2014 650 659 666    105
    "13254K" 19807 2014 650 659 666    105
    "13254K" 19808 2014 650 659 666 105.87
    "13254K" 19809 2014 650 659 666 105.87
    "13254K" 19810 2014 650 659 666 105.87
    "13254K" 19813 2014 650 659 666 104.13
    "13254K" 19814 2014 651 659 666 104.13
    "13254K" 19815 2014 651 659 666 109.38
    "13254K" 19816 2014 651 659 666 103.25
    "13254K" 19817 2014 651 659 666 104.13
    "13254K" 19820 2014 651 659 666 104.13
    "13254K" 19821 2014 651 659 666    105
    "13254K" 19822 2014 651 659 666 106.75
    "13254K" 19823 2014 651 659 666  108.5
    "13254K" 19824 2014 651 659 666  108.5
    "13254K" 19827 2014 651 659 666  108.5
    "13254K" 19828 2014 651 659 666  108.5
    "13254K" 19829 2014 651 659 666  108.5
    "13254K" 19830 2014 651 659 666  108.5
    "13254K" 19831 2014 651 659 666  108.5
    "13254K" 19834 2014 651 659 666  108.5
    "13254K" 19835 2014 651 659 666  108.5
    "13254K" 19836 2014 651 659 666  108.5
    "13254K" 19837 2014 651 659 666  108.5
    "13254K" 19838 2014 651 659 666 105.87
    "13254K" 19841 2014 651 659 666 105.87
    "13254K" 19842 2014 651 659 666 105.87
    "13254K" 19843 2014 651 659 666 109.38
    "13254K" 19844 2014 652 659 666 109.38
    "13254K" 19845 2014 652 659 666 103.25
    "13254K" 19848 2014 652 659 666  108.5
    "13254K" 19849 2014 652 659 666 103.25
    "13254K" 19850 2014 652 659 666 102.37
    "13254K" 19851 2014 652 659 666    105
    "13254K" 19852 2014 652 659 666 102.37
    "13254K" 19855 2014 652 659 666  108.5
    "13254K" 19856 2014 652 659 666 103.25
    "13254K" 19857 2014 652 659 666  101.5
    "13254K" 19858 2014 652 659 666 100.63
    "13254K" 19859 2014 652 659 666  101.5
    "13254K" 19862 2014 652 659 666     98
    "13254K" 19863 2014 652 659 666    105
    "13254K" 19864 2014 652 659 666    105
    "13254K" 19865 2014 652 659 666    105
    "13254K" 19866 2014 652 659 666 103.25
    "13254K" 19869 2014 652 659 666  99.75
    "13254K" 19870 2014 652 659 666  97.13
    "13254K" 19871 2014 652 659 666     98
    "13254K" 19872 2014 652 659 666     98
    "13254K" 19873 2014 652 659 666  98.87
    "13254K" 19876 2014 653 659 666     98
    "13254K" 19877 2014 653 659 666     98
    "13254K" 19878 2014 653 659 666  98.87
    "13254K" 19879 2014 653 659 666     98
    "13254K" 19880 2014 653 659 666     98
    "13254K" 19883 2014 653 659 666     98
    "13254K" 19884 2014 653 659 666  101.5
    "13254K" 19885 2014 653 659 666 102.37
    "13254K" 19886 2014 653 659 666 102.37
    "13254K" 19887 2014 653 659 666  96.25
    "13254K" 19890 2014 653 659 666  96.25
    "13254K" 19891 2014 653 659 666  101.5
    "13254K" 19892 2014 653 659 666  101.5
    "13254K" 19893 2014 653 659 666  101.5
    "13254K" 19894 2014 653 659 666 102.37
    "13254K" 19897 2014 653 659 666 103.25
    "13254K" 19898 2014 653 659 666 103.25
    "13254K" 19899 2014 653 659 666 103.25
    "13254K" 19900 2014 653 659 666  101.5
    "13254K" 19901 2014 653 659 666  97.13
    "13254K" 19904 2014 653 659 666 102.37
    "13254K" 19905 2014 654 659 666  98.87
    "13254K" 19906 2014 654 659 666 102.37
    "13254K" 19907 2014 654 659 666 102.37
    "13254K" 19908 2014 654 659 666  96.25
    "13254K" 19911 2014 654 659 666  96.25
    "13254K" 19912 2014 654 659 666  101.5
    "13254K" 19913 2014 654 659 666  101.5
    "13254K" 19914 2014 654 659 666  101.5
    "13254K" 19915 2014 654 659 666 102.37
    "13254K" 19918 2014 654 659 666 102.37
    "13254K" 19919 2014 654 659 666 102.37
    "13254K" 19920 2014 654 659 666 102.37
    "13254K" 19921 2014 654 659 666 103.25
    "13254K" 19922 2014 654 659 666  96.25
    "13254K" 19925 2014 654 659 666  101.5
    "13254K" 19926 2014 654 659 666 100.63
    "13254K" 19927 2014 654 659 666  98.87
    "13254K" 19928 2014 654 659 666  101.5
    "13254K" 19929 2014 654 659 666  101.5
    "13254K" 19932 2014 654 659 666  98.87
    "13254K" 19933 2014 654 659 666  98.87
    "13254K" 19934 2014 654 659 666  98.87
    "13254K" 19935 2014 654 659 666  101.5
    "13254K" 19936 2014 655 659 666 103.25
    "13254K" 19939 2014 655 659 666  99.75
    "13254K" 19940 2014 655 659 666 103.25
    "13254K" 19941 2014 655 659 666 102.37
    "13254K" 19942 2014 655 659 666 103.25
    "13254K" 19943 2014 655 659 666 103.25
    "13254K" 19946 2014 655 659 666 103.25
    "13254K" 19947 2014 655 659 666 103.25
    "13254K" 19948 2014 655 659 666    105
    "13254K" 19949 2014 655 659 666    105
    "13254K" 19950 2014 655 659 666    105
    "13254K" 19953 2014 655 659 666    105
    "13254K" 19954 2014 655 659 666    105
    "13254K" 19955 2014 655 659 666    105
    "13254K" 19956 2014 655 659 666 106.75
    "13254K" 19957 2014 655 659 666 107.63
    "13254K" 19960 2014 655 659 666    105
    "13254K" 19961 2014 655 659 666    105
    "13254K" 19962 2014 655 659 666  108.5
    "13254K" 19963 2014 655 659 666 107.63
    "13254K" 19964 2014 655 659 666 106.75
    "13254K" 19967 2014 656 659 666 109.38
    "13254K" 19968 2014 656 659 666 109.38
    "13254K" 19969 2014 656 659 666 106.75
    "13254K" 19970 2014 656 659 666 106.75
    "13254K" 19971 2014 656 659 666 106.75
    "13254K" 19974 2014 656 659 666 106.75
    "13254K" 19975 2014 656 659 666 106.75
    "13254K" 19976 2014 656 659 666    105
    "13254K" 19977 2014 656 659 666    105
    "13254K" 19978 2014 656 659 666    105
    "13254K" 19981 2014 656 659 666    105
    "13254K" 19982 2014 656 659 666  101.5
    "13254K" 19983 2014 656 659 666 100.63
    "13254K" 19984 2014 656 659 666 100.63
    "13254K" 19985 2014 656 659 666 100.63
    "13254K" 19988 2014 656 659 666    105
    "13254K" 19989 2014 656 659 666    105
    "13254K" 19990 2014 656 659 666    105
    "13254K" 19991 2014 656 659 666    105
    "13254K" 19992 2014 656 659 666 109.38
    "13254K" 19995 2014 656 659 666 107.63
    "13254K" 19996 2014 656 659 666 104.13
    "13254K" 19997 2014 657 659 666 104.13
    "13254K" 19998 2014 657 659 666 103.25
    "13254K" 19999 2014 657 659 666 103.25
    "13254K" 20002 2014 657 659 666 104.13
    "13254K" 20003 2014 657 659 666 104.13
    "13254K" 20004 2014 657 659 666 104.13
    "13254K" 20005 2014 657 659 666 102.37
    "13254K" 20006 2014 657 659 666  101.5
    "13254K" 20009 2014 657 659 666  101.5
    "13254K" 20010 2014 657 659 666 102.37
    "13254K" 20011 2014 657 659 666 103.25
    "13254K" 20012 2014 657 659 666 103.25
    "13254K" 20013 2014 657 659 666 102.37
    "13254K" 20016 2014 657 659 666 102.37
    "13254K" 20017 2014 657 659 666  101.5
    "13254K" 20018 2014 657 659 666  98.87
    "13254K" 20019 2014 657 659 666 102.37
    "13254K" 20020 2014 657 659 666     98
    "13254K" 20023 2014 657 659 666     98
    "13254K" 20024 2014 657 659 666     98
    "13254K" 20025 2014 657 659 666  93.63
    "13254K" 20026 2014 657 659 666  98.87
    "13254K" 20027 2014 657 659 666  99.75
    "13254K" 20030 2014 658 659 666  99.75
    "13254K" 20031 2014 658 659 666  97.13
    "13254K" 20032 2014 658 659 666  97.13
    "13254K" 20033 2014 658 659 666     98
    "13254K" 20034 2014 658 659 666  95.38
    "13254K" 20037 2014 658 659 666 103.25
    "13254K" 20038 2014 658 659 666 103.25
    "13254K" 20039 2014 658 659 666 103.25
    "13254K" 20040 2014 658 659 666  99.75
    "13254K" 20041 2014 658 659 666  95.38
    "13254K" 20044 2014 658 659 666  99.75
    "13254K" 20045 2014 658 659 666     98
    "13254K" 20046 2014 658 659 666  99.75
    "13254K" 20047 2014 658 659 666  99.75
    "13254K" 20048 2014 658 659 666  99.75
    "13254K" 20051 2014 658 659 666  99.75
    "13254K" 20052 2014 658 659 666  99.75
    "13254K" 20053 2014 658 659 666  99.75
    "13254K" 20054 2014 658 659 666  99.75
    "13254K" 20055 2014 658 659 666  98.88
    "13254K" 20058 2014 659 659 666  98.88
    "13254K" 20059 2014 659 659 666  98.88
    "13254K" 20060 2014 659 659 666     98
    "13254K" 20061 2014 659 659 666 102.38
    "13254K" 20062 2014 659 659 666  92.75
    "13254K" 20065 2014 659 659 666  96.25
    "13254K" 20066 2014 659 659 666  93.63
    "13254K" 20067 2014 659 659 666  92.75
    "13254K" 20068 2014 659 659 666  95.38
    "13254K" 20069 2014 659 659 666  91.88
    "13254K" 20072 2014 659 659 666  96.25
    "13254K" 20073 2014 659 659 666  95.38
    "13254K" 20074 2014 659 659 666  89.25
    "13254K" 20075 2014 659 659 666   87.5
    "13254K" 20076 2014 659 659 666  88.38
    "13254K" 20079 2014 659 659 666  91.88
    "13254K" 20080 2014 659 659 666  91.88
    "13254K" 20081 2014 659 659 666  91.88
    "13254K" 20082 2014 659 659 666  91.88
    "13254K" 20083 2014 659 659 666  91.88
    "13254K" 20086 2014 659 659 666  89.25
    "13254K" 20087 2014 659 659 666  89.25
    "13254K" 20088 2014 659 659 666  89.25
    "13254K" 20089 2015 660 671 678  89.25
    "13254K" 20090 2015 660 671 678  89.25
    "13254K" 20093 2015 660 671 678   87.5
    "13254K" 20094 2015 660 671 678   87.5
    "13254K" 20095 2015 660 671 678   87.5
    "13254K" 20096 2015 660 671 678   87.5
    "13254K" 20097 2015 660 671 678   87.5
    "13254K" 20100 2015 660 671 678     91
    "13254K" 20101 2015 660 671 678   87.5
    "13254K" 20102 2015 660 671 678   87.5
    "13254K" 20103 2015 660 671 678   87.5
    "13254K" 20104 2015 660 671 678   87.5
    "13254K" 20107 2015 660 671 678   87.5
    "13254K" 20108 2015 660 671 678   87.5
    "13254K" 20109 2015 660 671 678  84.88
    "13254K" 20110 2015 660 671 678   87.5
    end
    format %tdDD/NN/CCYY Date
    format %tmNN/CCYY MONTH
    format %tmNN/CCYY FYE_month
    format %tmNN/CCYY measurement_month
    Edit: fixed data example.

    Last edited by Mark Tredwitz; 14 Jan 2019, 07:49.

  • #2
    I think that just taking the average by -measurement month- will do the trick here:

    Code:
    . egen futuremv = mean(MV), by( measurement_month)


    Comment


    • #3
      Thanks for your data example. I would try rangestat (SSC) on this. This code follows yours.


      Code:
      rangestat MV, int(MONTH measurement_month measurement_month) by(DSCD) 
      
      tabdisp MONTH, c(measurement_month MV_mean) 
      
      ------------------------------------------------
          MONTH | measurement_month         mean of MV
      ----------+-------------------------------------
        12/2011 |                              76.9983
        01/2012 |           07/2013          74.046087
        02/2012 |           07/2013          74.046087
        03/2012 |           07/2013          74.046087
        04/2012 |           07/2013          74.046087
        05/2012 |           07/2013          74.046087
        06/2012 |           07/2013          74.046087
        07/2012 |           07/2013          74.046087
        08/2012 |           07/2013          74.046087
        09/2012 |           07/2013          74.046087
        10/2012 |           07/2013          74.046087
        11/2012 |           07/2013          74.046087
        12/2012 |           07/2013          74.046087
        01/2013 |           07/2014           100.5087
        02/2013 |           07/2014           100.5087
        03/2013 |           07/2014           100.5087
        04/2013 |           07/2014           100.5087
        05/2013 |           07/2014           100.5087
        06/2013 |           07/2014           100.5087
        07/2013 |           07/2014           100.5087
        08/2013 |           07/2014           100.5087
        09/2013 |           07/2014           100.5087
        10/2013 |           07/2014           100.5087
        11/2013 |           07/2014           100.5087
        12/2013 |           07/2014           100.5087
        01/2014 |           07/2015                   
        02/2014 |           07/2015                   
        03/2014 |           07/2015                   
        04/2014 |           07/2015                   
        05/2014 |           07/2015                   
        06/2014 |           07/2015                   
        07/2014 |           07/2015                   
        08/2014 |           07/2015                   
        09/2014 |           07/2015                   
        10/2014 |           07/2015                   
        11/2014 |           07/2015                   
        12/2014 |           07/2015                   
        01/2015 |           07/2016                   
      ------------------------------------------------

      Comment


      • #4
        Originally posted by Joro Kolev View Post
        I think that just taking the average by -measurement month- will do the trick here:

        Code:
        . egen futuremv = mean(MV), by( measurement_month)

        Thanks fop the quick reply, but that unfortunately does not work. Maybe the initial explanations were not good enough, so I'll try to clarify:

        a) this would pool observations for all firms that have this particular month as a measuremernt month
        b) this does not help with the "mapping" of MV from future observations to past observations

        The variable measurement_month tells me which is the relevant month to obtain my MV data from.

        Say we look at the second observation from the data above (first one is kinda bad due to missing values):
        Code:
         input str6 DSCD float(Date YEAR MONTH FYE_month measurement_month) double MV  
         13254K" 18994 2012 624 635 642  44.63
        This observation is from January 2nd, 2012. MV is the market value on January 2nd, 2012. Fiscal year end is December 31, 2012. Thus, measurement_month tells me that for my new variable MV_future, I'd like to find out the mean MV in July 2013. I have the observations for July 2013 further down below in my dataset, but I don't lnow how to tell the mean() function to only look at these observations.


        Nick Cox Sorry, just saw your reply, the results semem right as they align with what I fiddled together in Excel for validation purposes. I'll give rangestat a try and report back, but thank you very much already!

        Edit2: Works like a charm, thanks a heap!
        Last edited by Mark Tredwitz; 14 Jan 2019, 08:43.

        Comment

        Working...
        X