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:

* dataex DSCD Date YEAR MONTH FYE_month measurement_month, count(400)
* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 DSCD float(Date YEAR MONTH FYE_month measurement_month)
"13254K" 18991 2011 623   .   .
"13254K" 18994 2012 624 635 642
"13254K" 18995 2012 624 635 642
"13254K" 18996 2012 624 635 642
"13254K" 18997 2012 624 635 642
"13254K" 18998 2012 624 635 642
"13254K" 19001 2012 624 635 642
"13254K" 19002 2012 624 635 642
"13254K" 19003 2012 624 635 642
"13254K" 19004 2012 624 635 642
"13254K" 19005 2012 624 635 642
"13254K" 19008 2012 624 635 642
"13254K" 19009 2012 624 635 642
"13254K" 19010 2012 624 635 642
"13254K" 19011 2012 624 635 642
"13254K" 19012 2012 624 635 642
"13254K" 19015 2012 624 635 642
"13254K" 19016 2012 624 635 642
"13254K" 19017 2012 624 635 642
"13254K" 19018 2012 624 635 642
"13254K" 19019 2012 624 635 642
"13254K" 19022 2012 624 635 642
"13254K" 19023 2012 624 635 642
"13254K" 19024 2012 625 635 642
"13254K" 19025 2012 625 635 642
"13254K" 19026 2012 625 635 642
"13254K" 19029 2012 625 635 642
"13254K" 19030 2012 625 635 642
"13254K" 19031 2012 625 635 642
"13254K" 19032 2012 625 635 642
"13254K" 19033 2012 625 635 642
"13254K" 19036 2012 625 635 642
"13254K" 19037 2012 625 635 642
"13254K" 19038 2012 625 635 642
"13254K" 19039 2012 625 635 642
"13254K" 19040 2012 625 635 642
"13254K" 19043 2012 625 635 642
"13254K" 19044 2012 625 635 642
"13254K" 19045 2012 625 635 642
"13254K" 19046 2012 625 635 642
"13254K" 19047 2012 625 635 642
"13254K" 19050 2012 625 635 642
"13254K" 19051 2012 625 635 642
"13254K" 19052 2012 625 635 642
"13254K" 19053 2012 626 635 642
"13254K" 19054 2012 626 635 642
"13254K" 19057 2012 626 635 642
"13254K" 19058 2012 626 635 642
"13254K" 19059 2012 626 635 642
"13254K" 19060 2012 626 635 642
"13254K" 19061 2012 626 635 642
"13254K" 19064 2012 626 635 642
"13254K" 19065 2012 626 635 642
"13254K" 19066 2012 626 635 642
"13254K" 19067 2012 626 635 642
"13254K" 19068 2012 626 635 642
"13254K" 19071 2012 626 635 642
"13254K" 19072 2012 626 635 642
"13254K" 19073 2012 626 635 642
"13254K" 19074 2012 626 635 642
"13254K" 19075 2012 626 635 642
"13254K" 19078 2012 626 635 642
"13254K" 19079 2012 626 635 642
"13254K" 19080 2012 626 635 642
"13254K" 19081 2012 626 635 642
"13254K" 19082 2012 626 635 642
"13254K" 19085 2012 627 635 642
"13254K" 19086 2012 627 635 642
"13254K" 19087 2012 627 635 642
"13254K" 19088 2012 627 635 642
"13254K" 19089 2012 627 635 642
"13254K" 19092 2012 627 635 642
"13254K" 19093 2012 627 635 642
"13254K" 19094 2012 627 635 642
"13254K" 19095 2012 627 635 642
"13254K" 19096 2012 627 635 642
"13254K" 19099 2012 627 635 642
"13254K" 19100 2012 627 635 642
"13254K" 19101 2012 627 635 642
"13254K" 19102 2012 627 635 642
"13254K" 19103 2012 627 635 642
"13254K" 19106 2012 627 635 642
"13254K" 19107 2012 627 635 642
"13254K" 19108 2012 627 635 642
"13254K" 19109 2012 627 635 642
"13254K" 19110 2012 627 635 642
"13254K" 19113 2012 627 635 642
"13254K" 19114 2012 628 635 642
"13254K" 19115 2012 628 635 642
"13254K" 19116 2012 628 635 642
"13254K" 19117 2012 628 635 642
"13254K" 19120 2012 628 635 642
"13254K" 19121 2012 628 635 642
"13254K" 19122 2012 628 635 642
"13254K" 19123 2012 628 635 642
"13254K" 19124 2012 628 635 642
"13254K" 19127 2012 628 635 642
"13254K" 19128 2012 628 635 642
"13254K" 19129 2012 628 635 642
"13254K" 19130 2012 628 635 642
"13254K" 19131 2012 628 635 642
"13254K" 19134 2012 628 635 642
"13254K" 19135 2012 628 635 642
"13254K" 19136 2012 628 635 642
"13254K" 19137 2012 628 635 642
"13254K" 19138 2012 628 635 642
"13254K" 19141 2012 628 635 642
"13254K" 19142 2012 628 635 642
"13254K" 19143 2012 628 635 642
"13254K" 19144 2012 628 635 642
"13254K" 19145 2012 629 635 642
"13254K" 19148 2012 629 635 642
"13254K" 19149 2012 629 635 642
"13254K" 19150 2012 629 635 642
"13254K" 19151 2012 629 635 642
"13254K" 19152 2012 629 635 642
"13254K" 19155 2012 629 635 642
"13254K" 19156 2012 629 635 642
"13254K" 19157 2012 629 635 642
"13254K" 19158 2012 629 635 642
"13254K" 19159 2012 629 635 642
"13254K" 19162 2012 629 635 642
"13254K" 19163 2012 629 635 642
"13254K" 19164 2012 629 635 642
"13254K" 19165 2012 629 635 642
"13254K" 19166 2012 629 635 642
"13254K" 19169 2012 629 635 642
"13254K" 19170 2012 629 635 642
"13254K" 19171 2012 629 635 642
"13254K" 19172 2012 629 635 642
"13254K" 19173 2012 629 635 642
"13254K" 19176 2012 630 635 642
"13254K" 19177 2012 630 635 642
"13254K" 19178 2012 630 635 642
"13254K" 19179 2012 630 635 642
"13254K" 19180 2012 630 635 642
"13254K" 19183 2012 630 635 642
"13254K" 19184 2012 630 635 642
"13254K" 19185 2012 630 635 642
"13254K" 19186 2012 630 635 642
"13254K" 19187 2012 630 635 642
"13254K" 19190 2012 630 635 642
"13254K" 19191 2012 630 635 642
"13254K" 19192 2012 630 635 642
"13254K" 19193 2012 630 635 642
"13254K" 19194 2012 630 635 642
"13254K" 19197 2012 630 635 642
"13254K" 19198 2012 630 635 642
"13254K" 19199 2012 630 635 642
"13254K" 19200 2012 630 635 642
"13254K" 19201 2012 630 635 642
"13254K" 19204 2012 630 635 642
"13254K" 19205 2012 630 635 642
"13254K" 19206 2012 631 635 642
"13254K" 19207 2012 631 635 642
"13254K" 19208 2012 631 635 642
"13254K" 19211 2012 631 635 642
"13254K" 19212 2012 631 635 642
"13254K" 19213 2012 631 635 642
"13254K" 19214 2012 631 635 642
"13254K" 19215 2012 631 635 642
"13254K" 19218 2012 631 635 642
"13254K" 19219 2012 631 635 642
"13254K" 19220 2012 631 635 642
"13254K" 19221 2012 631 635 642
"13254K" 19222 2012 631 635 642
"13254K" 19225 2012 631 635 642
"13254K" 19226 2012 631 635 642
"13254K" 19227 2012 631 635 642
"13254K" 19228 2012 631 635 642
"13254K" 19229 2012 631 635 642
"13254K" 19232 2012 631 635 642
"13254K" 19233 2012 631 635 642
"13254K" 19234 2012 631 635 642
"13254K" 19235 2012 631 635 642
"13254K" 19236 2012 631 635 642
"13254K" 19239 2012 632 635 642
"13254K" 19240 2012 632 635 642
"13254K" 19241 2012 632 635 642
"13254K" 19242 2012 632 635 642
"13254K" 19243 2012 632 635 642
"13254K" 19246 2012 632 635 642
"13254K" 19247 2012 632 635 642
"13254K" 19248 2012 632 635 642
"13254K" 19249 2012 632 635 642
"13254K" 19250 2012 632 635 642
"13254K" 19253 2012 632 635 642
"13254K" 19254 2012 632 635 642
"13254K" 19255 2012 632 635 642
"13254K" 19256 2012 632 635 642
"13254K" 19257 2012 632 635 642
"13254K" 19260 2012 632 635 642
"13254K" 19261 2012 632 635 642
"13254K" 19262 2012 632 635 642
"13254K" 19263 2012 632 635 642
"13254K" 19264 2012 632 635 642
"13254K" 19267 2012 633 635 642
"13254K" 19268 2012 633 635 642
"13254K" 19269 2012 633 635 642
"13254K" 19270 2012 633 635 642
"13254K" 19271 2012 633 635 642
"13254K" 19274 2012 633 635 642
"13254K" 19275 2012 633 635 642
"13254K" 19276 2012 633 635 642
"13254K" 19277 2012 633 635 642
"13254K" 19278 2012 633 635 642
"13254K" 19281 2012 633 635 642
"13254K" 19282 2012 633 635 642
"13254K" 19283 2012 633 635 642
"13254K" 19284 2012 633 635 642
"13254K" 19285 2012 633 635 642
"13254K" 19288 2012 633 635 642
"13254K" 19289 2012 633 635 642
"13254K" 19290 2012 633 635 642
"13254K" 19291 2012 633 635 642
"13254K" 19292 2012 633 635 642
"13254K" 19295 2012 633 635 642
"13254K" 19296 2012 633 635 642
"13254K" 19297 2012 633 635 642
"13254K" 19298 2012 634 635 642
"13254K" 19299 2012 634 635 642
"13254K" 19302 2012 634 635 642
"13254K" 19303 2012 634 635 642
"13254K" 19304 2012 634 635 642
"13254K" 19305 2012 634 635 642
"13254K" 19306 2012 634 635 642
"13254K" 19309 2012 634 635 642
"13254K" 19310 2012 634 635 642
"13254K" 19311 2012 634 635 642
"13254K" 19312 2012 634 635 642
"13254K" 19313 2012 634 635 642
"13254K" 19316 2012 634 635 642
"13254K" 19317 2012 634 635 642
"13254K" 19318 2012 634 635 642
"13254K" 19319 2012 634 635 642
"13254K" 19320 2012 634 635 642
"13254K" 19323 2012 634 635 642
"13254K" 19324 2012 634 635 642
"13254K" 19325 2012 634 635 642
"13254K" 19326 2012 634 635 642
"13254K" 19327 2012 634 635 642
"13254K" 19330 2012 635 635 642
"13254K" 19331 2012 635 635 642
"13254K" 19332 2012 635 635 642
"13254K" 19333 2012 635 635 642
"13254K" 19334 2012 635 635 642
"13254K" 19337 2012 635 635 642
"13254K" 19338 2012 635 635 642
"13254K" 19339 2012 635 635 642
"13254K" 19340 2012 635 635 642
"13254K" 19341 2012 635 635 642
"13254K" 19344 2012 635 635 642
"13254K" 19345 2012 635 635 642
"13254K" 19346 2012 635 635 642
"13254K" 19347 2012 635 635 642
"13254K" 19348 2012 635 635 642
"13254K" 19351 2012 635 635 642
"13254K" 19352 2012 635 635 642
"13254K" 19353 2012 635 635 642
"13254K" 19354 2012 635 635 642
"13254K" 19355 2012 635 635 642
"13254K" 19358 2012 635 635 642
"13254K" 19359 2013 636 647 654
"13254K" 19360 2013 636 647 654
"13254K" 19361 2013 636 647 654
"13254K" 19362 2013 636 647 654
"13254K" 19365 2013 636 647 654
"13254K" 19366 2013 636 647 654
"13254K" 19367 2013 636 647 654
"13254K" 19368 2013 636 647 654
"13254K" 19369 2013 636 647 654
"13254K" 19372 2013 636 647 654
"13254K" 19373 2013 636 647 654
"13254K" 19374 2013 636 647 654
"13254K" 19375 2013 636 647 654
"13254K" 19376 2013 636 647 654
"13254K" 19379 2013 636 647 654
"13254K" 19380 2013 636 647 654
"13254K" 19381 2013 636 647 654
"13254K" 19382 2013 636 647 654
"13254K" 19383 2013 636 647 654
"13254K" 19386 2013 636 647 654
"13254K" 19387 2013 636 647 654
"13254K" 19388 2013 636 647 654
"13254K" 19389 2013 636 647 654
"13254K" 19390 2013 637 647 654
"13254K" 19393 2013 637 647 654
"13254K" 19394 2013 637 647 654
"13254K" 19395 2013 637 647 654
"13254K" 19396 2013 637 647 654
"13254K" 19397 2013 637 647 654
"13254K" 19400 2013 637 647 654
"13254K" 19401 2013 637 647 654
"13254K" 19402 2013 637 647 654
"13254K" 19403 2013 637 647 654
"13254K" 19404 2013 637 647 654
"13254K" 19407 2013 637 647 654
"13254K" 19408 2013 637 647 654
"13254K" 19409 2013 637 647 654
"13254K" 19410 2013 637 647 654
"13254K" 19411 2013 637 647 654
"13254K" 19414 2013 637 647 654
"13254K" 19415 2013 637 647 654
"13254K" 19416 2013 637 647 654
"13254K" 19417 2013 637 647 654
"13254K" 19418 2013 638 647 654
"13254K" 19421 2013 638 647 654
"13254K" 19422 2013 638 647 654
"13254K" 19423 2013 638 647 654
"13254K" 19424 2013 638 647 654
"13254K" 19425 2013 638 647 654
"13254K" 19428 2013 638 647 654
"13254K" 19429 2013 638 647 654
"13254K" 19430 2013 638 647 654
"13254K" 19431 2013 638 647 654
"13254K" 19432 2013 638 647 654
"13254K" 19435 2013 638 647 654
"13254K" 19436 2013 638 647 654
"13254K" 19437 2013 638 647 654
"13254K" 19438 2013 638 647 654
"13254K" 19439 2013 638 647 654
"13254K" 19442 2013 638 647 654
"13254K" 19443 2013 638 647 654
"13254K" 19444 2013 638 647 654
"13254K" 19445 2013 638 647 654
"13254K" 19446 2013 638 647 654
"13254K" 19449 2013 639 647 654
"13254K" 19450 2013 639 647 654
"13254K" 19451 2013 639 647 654
"13254K" 19452 2013 639 647 654
"13254K" 19453 2013 639 647 654
"13254K" 19456 2013 639 647 654
"13254K" 19457 2013 639 647 654
"13254K" 19458 2013 639 647 654
"13254K" 19459 2013 639 647 654
"13254K" 19460 2013 639 647 654
"13254K" 19463 2013 639 647 654
"13254K" 19464 2013 639 647 654
"13254K" 19465 2013 639 647 654
"13254K" 19466 2013 639 647 654
"13254K" 19467 2013 639 647 654
"13254K" 19470 2013 639 647 654
"13254K" 19471 2013 639 647 654
"13254K" 19472 2013 639 647 654
"13254K" 19473 2013 639 647 654
"13254K" 19474 2013 639 647 654
"13254K" 19477 2013 639 647 654
"13254K" 19478 2013 639 647 654
"13254K" 19479 2013 640 647 654
"13254K" 19480 2013 640 647 654
"13254K" 19481 2013 640 647 654
"13254K" 19484 2013 640 647 654
"13254K" 19485 2013 640 647 654
"13254K" 19486 2013 640 647 654
"13254K" 19487 2013 640 647 654
"13254K" 19488 2013 640 647 654
"13254K" 19491 2013 640 647 654
"13254K" 19492 2013 640 647 654
"13254K" 19493 2013 640 647 654
"13254K" 19494 2013 640 647 654
"13254K" 19495 2013 640 647 654
"13254K" 19498 2013 640 647 654
"13254K" 19499 2013 640 647 654
"13254K" 19500 2013 640 647 654
"13254K" 19501 2013 640 647 654
"13254K" 19502 2013 640 647 654
"13254K" 19505 2013 640 647 654
"13254K" 19506 2013 640 647 654
"13254K" 19507 2013 640 647 654
"13254K" 19508 2013 640 647 654
"13254K" 19509 2013 640 647 654
"13254K" 19512 2013 641 647 654
"13254K" 19513 2013 641 647 654
"13254K" 19514 2013 641 647 654
"13254K" 19515 2013 641 647 654
"13254K" 19516 2013 641 647 654
"13254K" 19519 2013 641 647 654
"13254K" 19520 2013 641 647 654
"13254K" 19521 2013 641 647 654
"13254K" 19522 2013 641 647 654
"13254K" 19523 2013 641 647 654
"13254K" 19526 2013 641 647 654
"13254K" 19527 2013 641 647 654
"13254K" 19528 2013 641 647 654
"13254K" 19529 2013 641 647 654
"13254K" 19530 2013 641 647 654
"13254K" 19533 2013 641 647 654
"13254K" 19534 2013 641 647 654
"13254K" 19535 2013 641 647 654
"13254K" 19536 2013 641 647 654
"13254K" 19537 2013 641 647 654
"13254K" 19540 2013 642 647 654
"13254K" 19541 2013 642 647 654
"13254K" 19542 2013 642 647 654
"13254K" 19543 2013 642 647 654
"13254K" 19544 2013 642 647 654
"13254K" 19547 2013 642 647 654
"13254K" 19548 2013 642 647 654
"13254K" 19549 2013 642 647 654
"13254K" 19550 2013 642 647 654
end
format %tdDD/NN/CCYY Date
format %tmNN/CCYY MONTH
format %tmNN/CCYY FYE_month
format %tmNN/CCYY measurement_month

BJ Data Tech Solution

Home / Data Cleaning / Data management / Data Processing / Generate mean based on condition, where condition is based on variable
Generate mean based on condition, where condition is based on variable

0 Response to Generate mean based on condition, where condition is based on variable

Post a Comment

Home / Data Cleaning / Data management / Data Processing / Generate mean based on condition, where condition is based on variable Generate mean based on condition, where condition is based on variable

Related Posts with Generate mean based on condition, where condition is based on variable

0 Response to Generate mean based on condition, where condition is based on variable

Post a Comment

Home / Data Cleaning / Data management / Data Processing / Generate mean based on condition, where condition is based on variable
Generate mean based on condition, where condition is based on variable