generate event time

[River Huang]

Dear All, Suppose that I have daily trading data for many years. (A sample of two-year data is "here"). I wish to generate a variable, say, event, which is equal to 0 on March 15 for each year. For trading data befoer March 15, `event' is equal to -1, -2, and so on. Similarly, for trading data after March 15, `event' is equal to 1, 2, and so on. Any suggestions are highly appreciated.

[Nick Cox]

gen wanted = ymd - mdy(3, 15, year)

[River Huang]

Dear Nick, Thanks for this suggestion. My situation is a little more complicated since the raw (trading) data are with missing dates (no transactions, no `ymd' and `x'). To see this, consider

* Example generated by -dataex-. For more info, type help dataex
clear
input float(year ymd) double x
2020 21915                  .
2020 21916  4.078662808842372
2020 21917  4.327461219536327
2020 21920  4.105396042023531
2020 21921 3.9685619746345355
2020 21922  3.990100969148667
2020 21923  4.054143926300977
2020 21924  3.976806410843543
2020 21927  4.069649628737443
2020 21928  3.798537479169718
2020 21929 3.7870387351759622
2020 21930 3.7706507831354945
2020 21931 3.6828865929019905
2020 21934  3.557998880814887
2020 21935  3.633920317910169
2020 21936 3.5230487954989314
2020 21937 3.5127136303996123
2020 21938 3.5460735552903953
2020 21941   3.56616122659048
2020 21942  3.665540794834084
2020 21943  3.470945749853663
2020 21944  3.318103414599327
2020 21945  3.191709410136037
2020 21948   2.96651178387107
2020 21949  2.999386755411811
2020 21950  2.962854561633652
2020 21951  2.895958649823593
2020 21952 2.8525677098546387
2020 21955 2.6937264172886457
2020 21956 2.6577165682663924
2020 21957 2.6811240513996744
2020 21958  2.706792430292318
2020 21959  2.721496792724611
2020 21962 2.8824496125733146
2020 21963 2.9022966183487653
2020 21964  2.976775603172065
2020 21965  2.980451537322662
2020 21966  2.972797010757884
2020 21969 2.9196072978170404
2020 21970  2.931115184778893
2020 21971  2.975514282449155
2020 21972 2.9541645464506536
2020 21973 2.7817183910263386
2020 21976  2.853884336226433
2020 21977 2.9720976373539645
2020 21978  2.881623418840183
2020 21979  2.897738377825096
2020 21980 2.8510290465471164
2020 21983 2.8391753000567403
2020 21984  2.969829208977475
2020 21985 3.1004241467705973
2020 21986  3.021132325118304
2020 21987  2.944130743173796
2020 21990   2.81770637626973
2020 21991 2.6946068168930353
2020 21992  2.573113556589837
2020 21993   2.66018610705068
2020 21994 2.5890181525990856
2020 21997  2.496781975202953
2020 21998  2.538474537777966
2020 21999 2.4533430743205304
2020 22000 2.4221994687639166
2020 22001 2.2994548659575393
2020 22004 2.1107534289642955
2020 22005 2.0278167516029058
2020 22006 1.9851478760375234
2020 22007  2.017598247115218
2020 22008 1.9887147326792527
2020 22011  2.076549427149834
2020 22012 2.1060393170082583
2020 22013 2.0894225508004864
2020 22014  2.100152169016474
2020 22015                  .
2020 22018 2.1096146819181754
2020 22019 1.9591102806559586
2020 22020 1.8373011534588726
2020 22021  1.893375620940119
2020 22022   1.92000413581274
2020 22025 1.7162059607815063
2020 22026 1.7197261315496555
2020 22027 1.7797216352636096
2020 22028  1.707647356543461
2020 22029 1.5782305429977344
2020 22032 1.6331108027588666
2020 22033 1.7184405025265286
2020 22034 1.6506694740922039
2020 22035 1.7466384140911542
2020 22036 1.6604187842529525
2020 22039  1.718304572527422
2020 22040 1.6896694150579654
2020 22041 1.6872360418226604
2020 22042 1.7282719310446446
2020 22043 1.7347071002057888
2020 22046  1.639726782725153
2020 22047  1.495643932933604
2020 22048 1.4687490353397827
2020 22049 1.5319670397136658
2020 22050 1.5240198102615912
2020 22053 1.4059205183812693
2020 22054 1.3685332787912996
2020 22055 1.3192959009744232
2020 22056 1.1331094832166315
2020 22057 1.1515469831242722
2020 22060  1.153513278741267
2020 22061 1.2109986613631023
2020 22062 1.0878322660644393
2020 22063  1.027345115875475
2020 22064 1.1888877795134418
2020 22067  1.098065903977775
2020 22068 1.3690593692756425
2020 22069 1.5277309351524349
2020 22070 1.6108377480041076
2020 22071 1.7589053087448114
2020 22074 1.6232138286105977
2020 22075 1.4814914152891654
2020 22076 1.5116089419777066
2020 22077 1.6017710666045828
2020 22078 1.7504753622585512
2020 22081 1.8071909594893563
2020 22082 1.6614323749074682
2020 22083 1.6750431710749114
2020 22084 1.7430747749514814
2020 22085 1.7817854472761727
2020 22088 1.7235816857801607
2020 22089 1.8778214754519498
2020 22090 1.8181827984705898
2020 22091 1.6925378030831355
2020 22092 1.7168208706077233
2020 22095 1.9640843759237263
2020 22096  2.011159609359621
2020 22097 1.9610810897805486
2020 22098 1.9159838653074315
2020 22099 1.8863954844964586
2020 22102  1.902326998784522
2020 22103 1.9475854170008153
2020 22104 1.9003865986007082
2020 22105 1.8288456565468065
2020 22106 1.7253644664516539
2020 22109 1.5545003341759673
2020 22110 1.6229498236222086
2020 22111 1.7121714872973086
2020 22112 1.6300716046645825
2020 22113 1.6865712005507096
2020 22116 1.5459693255583646
2020 22117 1.6178825912113772
2020 22118 1.6615850233793386
2020 22119  1.745457436392184
2020 22120 1.7383101031833545
2020 22123  1.662818900036109
2020 22124 1.7054747678568096
2020 22125 1.7827657325760438
2020 22126 1.7624969203041414
2020 22127 2.0463452010494527
2020 22130   2.37812105044095
2020 22131 2.5862869435778997
2020 22132 2.6160880722633566
2020 22133 2.8415946386685067
2020 22134 2.8478579621459645
2020 22137 2.7317061065224104
2020 22138  2.683661985922591
2020 22139  2.626125132791286
2020 22140 2.6528568380202318
2020 22141 2.8797990968733025
2020 22144  2.945148527239981
2020 22145  3.112311060580619
2020 22146   3.01156996982035
2020 22147 2.9832699387764445
2020 22148 2.7552740025948563
2020 22151  3.032955971385021
2020 22152  3.236503617257105
2020 22153 3.3153361148921277
2020 22154  3.211933169866623
2020 22155  3.865227676911244
2020 22158  3.870914115916998
2020 22159 3.8959551792873217
2020 22160 3.8041303572259833
2020 22161  4.009367363853528
2020 22162  4.025303901901307
2020 22165 3.8539109092376127
2020 22166 3.6513928951981676
2020 22167  3.644090099280003
2020 22168 3.5467641717210707
2020 22169 3.5542762765652127
2020 22172 3.6455050902221435
2020 22173   3.73328433717221
2020 22174 3.8558720070030583
2020 22175  3.780041229256807
2020 22176 3.9210018577948023
2020 22179 3.9018781177912487
2020 22180 3.9712518681380513
2020 22181  4.105625094735689
2020 22182  4.002323695871203
2020 22183  4.067587813229889
2020 22186 4.3660549760091945
2020 22187 4.4064722475182005
2020 22188   4.78041220995514
2020 22189  4.731936765872742
2020 22190  4.690179314803275
2020 22193  4.882601066574303
2020 22194 4.7972134122137735
2020 22195  4.985196485947351
2020 22196  5.005227469810468
2020 22197  4.996803794887469
2020 22200  5.031415332375151
2020 22201  4.892706852272093
2020 22202   5.05406399103595
2020 22203  5.164051746278664
2020 22204  5.287411596117233
2020 22207  5.360670788261571
2020 22208  5.444015481291792
2020 22209  5.474113133168438
2020 22210  5.654185825123438
2020 22211  5.688649743934981
2020 22214 5.4948607552737805
2020 22215  5.495049997161766
2020 22216  5.384978487008186
2020 22217  5.287635501090832
2020 22218  5.370836811847333
2020 22221  5.167345411710134
2020 22222 5.1306507461099775
2020 22223  5.168892661797687
2020 22224  5.437778936571347
2020 22225  5.259914606942244
2020 22228  5.227771924465001
2020 22229  5.246973264613029
2020 22230  5.262549359941621
2020 22231  5.200075152497575
2020 22232  5.309947140949353
2020 22235   5.43555850104622
2020 22236  5.215983016167957
2020 22237  5.096307274070853
2020 22238  4.885378609373653
2020 22239  4.809489235996238
2020 22242   5.05803987602366
2020 22243  5.215865839079958
2020 22244  5.283830658820455
2020 22245  5.292648359551455
2020 22246 5.5492968117134325
2020 22249  5.817345309084889
2020 22250   5.80293548393057
2020 22251   5.82932615118374
2020 22252  5.538495189654627
2020 22253  5.721125040435329
2020 22256  5.633555892569828
2020 22257  5.628362931924076
2020 22258  5.671915607729649
2020 22259   6.00170319530746
2020 22260  6.129432846050813
2020 22263 6.4176520836313795
2020 22264  6.669950075056164
2020 22265  6.377286740735101
2020 22266  6.202782346385455
2020 22267  6.215662225832095
2020 22270  6.612936917466436
2020 22271  6.896593099726114
2020 22272  6.866044142128631
2020 22273  6.920214486420258
2020 22274                  .
2020 22277  6.869008809472443
2020 22278  7.493416424856145
2020 22279  7.605092015546348
2020 22280  7.712490966916545
2021 22281                  .
2021 22284   8.06777053446386
2021 22285  7.359854365457071
2021 22286  7.205237039532629
2021 22287  7.878643836778602
2021 22288  8.231577560408793
2021 22291  8.960871674879002
2021 22292 10.581029148151416
2021 22293  8.886079705913938
2021 22294  8.643804558523895
2021 22295  8.064051415064172
2021 22298  7.452969580471492
2021 22299  7.872911928770966
2021 22300  7.842906814913781
2021 22301  7.767611499138452
2021 22302  8.230918701733737
2021 22305   7.73194533399295
2021 22306  7.432539714993327
2021 22307  7.401669037020413
2021 22308  7.990334214577082
2021 22309  7.281012189661873
2021 22312  6.590331222508475
2021 22313  6.646793978639867
2021 22314  6.444423940391851
2021 22315  6.512811786434931
2021 22316  6.615118301589268
2021 22319  7.296528359765168
2021 22320 6.8699287819645765
2021 22321  6.627663716016593
2021 22322  6.137762633876656
2021 22323   6.20779234194216
2021 22326  5.780794679989306
2021 22327   5.90648901606173
2021 22328  5.790225452156135
2021 22329  6.155331307351939
2021 22330  5.899855013125739
2021 22333  5.629494824283393
2021 22334  5.737462605865199
2021 22335  5.822343895119623
2021 22336  5.646203289283516
2021 22337   5.54349751797426
2021 22340  5.697751827642464
2021 22341  5.652471701259643
2021 22342  5.412475371312307
2021 22343  5.534416716487077
2021 22344   5.70950800680299
2021 22347   5.58489864527613
2021 22348 5.9434959877765206
2021 22349  6.253152316008004
2021 22350 6.5167163883929735
2021 22351  6.613959232967181
2021 22354 6.2922144293220805
2021 22355 6.1190888374355605
2021 22356 6.3070956389634105
2021 22357  6.196764845237127
2021 22358  5.977156029569932
2021 22361  6.339249304070322
2021 22362  6.417058589640207
2021 22363  6.494830186887709
2021 22364  6.488775996472762
2021 22365 6.6709882390163555
2021 22368  6.468660685013636
2021 22369  6.622877102827899
2021 22370  6.450013893745923
2021 22371  6.529925175453857
2021 22372                  .
2021 22375  6.560786651123225
2021 22376   6.76961600641515
2021 22377  6.587184130145411
2021 22378 6.4819095668468645
2021 22379  6.423746868839266
2021 22382   6.75203694674197
2021 22383  6.681511917160072
2021 22384  6.734824319814859
2021 22385  6.946705876504115
2021 22386  7.073913771350272
2021 22389  7.340094154628825
2021 22390  7.360873815094403
2021 22391 7.3567719071450615
2021 22392  7.452823666319597
2021 22393   7.03683447503094
2021 22396  7.177422662077175
2021 22397  7.624049264330346
2021 22398  7.659751031370315
2021 22399   8.06966706189173
2021 22400  8.321828068078545
2021 22403  8.378557280006182
2021 22404  8.271560525808184
2021 22405  8.578043091759023
2021 22406  8.644891173396442
2021 22407  8.555531007781935
2021 22410  9.046923627244432
2021 22411  9.279170624424209
2021 22412  9.381986754245721
2021 22413  9.403150262019532
2021 22414  9.685980043582736
2021 22417  9.502804648927393
2021 22418  8.966132553304252
2021 22419  8.147330861564452
2021 22420  8.866663649464707
2021 22421  8.875682766734254
2021 22424  8.831167614154788
2021 22425  9.360646579064989
2021 22426  9.366411222316227
2021 22427  8.930721496514629
2021 22428  8.608622204659842
2021 22431  8.623306421640072
2021 22432  9.022671218602818
2021 22433   8.87393386842724
2021 22434  8.840923389291639
2021 22435  8.876462912063579
2021 22438   9.13828198766818
2021 22439  9.588448376067403
2021 22440    9.7829157912917
2021 22441  9.963497071771389
2021 22442  9.534882276900365
2021 22445  9.908635865432586
2021 22446  9.698961940224493
2021 22447  9.568137216428314
2021 22448  9.588266267779028
2021 22449  9.918811766312748
2021 22452 10.107598775903977
2021 22453 10.453602665613815
2021 22454 10.737561537221303
2021 22455 10.972074532759784
2021 22456 11.083997505069505
2021 22459 11.051280575064185
2021 22460 11.454398116634358
2021 22461 11.873958149885778
2021 22462 12.332387484614813
2021 22463 12.300173818648938
2021 22466 12.906369720554524
2021 22467 11.529444354769165
2021 22468 10.934616665410115
2021 22469 11.323540522892634
2021 22470 12.511843300545841
2021 22473 11.760238785040855
2021 22474 12.029347202401427
2021 22475  11.81991421882817
2021 22476 11.495075591009721
2021 22477  11.76794465488808
2021 22480 12.083984590951317
2021 22481 11.943338729311527
2021 22482 12.295361098275622
2021 22483 12.347854720923541
2021 22484 12.153688035497987
2021 22487 12.849613373436375
2021 22488 13.293484007037467
2021 22489 14.019880401311475
2021 22490 14.603278209649861
2021 22491 14.422175462847834
2021 22494  14.90605196419297
2021 22495 14.553107548749805
2021 22496 14.850444603430631
2021 22497 15.120241541833924
2021 22498 14.987265528992365
2021 22501 14.852985878793064
2021 22502  15.44203167077547
2021 22503 15.877845357723679
2021 22504 15.705855082460428
2021 22505 15.374083525864684
2021 22508  16.49078573002984
2021 22509 16.025984431681543
2021 22510 15.352550613036913
2021 22511  13.60372166345755
2021 22512 14.268942180363426
2021 22515 14.158125653937308
2021 22516  15.27837631305201
2021 22517 15.593436768459256
2021 22518 15.767535334526466
2021 22519 16.642358333920306
2021 22522 16.870301616348623
2021 22523 17.567191459465498
2021 22524 17.589634707827635
2021 22525  18.26031330649531
2021 22526  18.08593377174721
2021 22529 18.554769071407584
2021 22530 18.809457229414992
2021 22531 19.232146799185415
2021 22532  19.66714207367525
2021 22533 20.138971952073028
2021 22536 21.207199314893085
2021 22537 22.777502448187217
2021 22538  24.53031508732059
2021 22539  21.83709089452204
2021 22540 22.372460562856993
2021 22543 25.902048622552147
2021 22544 25.420668912699554
2021 22545 24.678357983635035
2021 22546 23.932878758342373
2021 22547 24.057219832291064
2021 22550 26.076560486252784
2021 22551  26.44261212357008
2021 22552  28.88471299186514
2021 22553  33.83880149896519
2021 22554  32.67852656290434
2021 22557 33.461620199914655
2021 22558  40.05959856089761
2021 22559  37.13423341251664
2021 22560  33.43082963951267
2021 22561 30.245735335063124
2021 22564   29.4433398710445
2021 22565 29.500705025083963
2021 22566  32.19830193918623
2021 22567 35.222395004242685
2021 22568  32.22416651099376
2021 22571 32.572924365168355
2021 22572 31.165173037684827
2021 22573  32.31744103649852
2021 22574 30.534573981470388
2021 22575  30.14658419490813
2021 22578 30.731847043877703
2021 22579  30.50606991198701
2021 22580  30.05168086362181
2021 22581 26.484952581946825
2021 22582 22.725124957958368
2021 22585 22.882122885304444
2021 22586 23.592135797873553
2021 22587 26.919140931089952
2021 22588 25.578915973482847
2021 22589 25.838065984928612
2021 22592 27.512506707088352
2021 22593  25.37060847313735
2021 22594  24.12325949757885
2021 22595  25.85021851572481
2021 22596  26.03004231753877
2021 22599 27.481777653897353
2021 22600  32.21632493667301
2021 22601   32.2209765662558
2021 22602  32.35365268780597
2021 22603 29.528470831741924
2021 22606 28.284788620184973
2021 22607 30.642744649563504
2021 22608 31.370705822757177
2021 22609 31.126194296331008
2021 22610  29.61912463394873
2021 22613 32.008043647789854
2021 22614 31.690733666916945
2021 22615  32.47036712748599
2021 22616  32.21482747880039
2021 22617  30.28629803376899
2021 22620 30.519168831673692
2021 22621  32.31150224114455
2021 22622 34.390258737926246
2021 22623 33.855153251451355
2021 22624  35.67415091849597
2021 22627  38.93006336606402
2021 22628  42.81247966787631
2021 22629 44.438439448390675
2021 22630   47.8804828338911
2021 22631  45.65870203293302
2021 22634  49.10023025324128
2021 22635 59.934710390557505
2021 22636  57.72340358672909
2021 22637  43.65284983097348
2021 22638 35.848657351529006
2021 22641 35.998066921023145
2021 22642 35.978718322839306
2021 22643 31.451997728044997
2021 22644  29.24905882025871
2021 22645  23.08806434950124
end
format %td ymd

gen wanted = ymd - mdy(2, 24, year)
list in 31/50

We have

. list in 31/50

     +---------------------------------------+
     | year         ymd           x   wanted |
     |---------------------------------------|
 31. | 2020   12feb2020   2.6811241      -12 |
 32. | 2020   13feb2020   2.7067924      -11 |
 33. | 2020   14feb2020   2.7214968      -10 |
 34. | 2020   17feb2020   2.8824496       -7 |
 35. | 2020   18feb2020   2.9022966       -6 |
     |---------------------------------------|
 36. | 2020   19feb2020   2.9767756       -5 |
 37. | 2020   20feb2020   2.9804515       -4 |
 38. | 2020   21feb2020    2.972797       -3 |
 39. | 2020   24feb2020   2.9196073        0 |
 40. | 2020   25feb2020   2.9311152        1 |
     |---------------------------------------|
 41. | 2020   26feb2020   2.9755143        2 |
 42. | 2020   27feb2020   2.9541645        3 |
 43. | 2020   28feb2020   2.7817184        4 |
 44. | 2020   02mar2020   2.8538843        7 |
 45. | 2020   03mar2020   2.9720976        8 |
     |---------------------------------------|
 46. | 2020   04mar2020   2.8816234        9 |
 47. | 2020   05mar2020   2.8977384       10 |
 48. | 2020   06mar2020    2.851029       11 |
 49. | 2020   09mar2020   2.8391753       14 |
 50. | 2020   10mar2020   2.9698292       15 |
     +---------------------------------------+

I expect the `wanted' of 38 (2020 21feb2020) to be -1 (instead of -3), and so on. Similarly, I expect the `wanted' of 44 (2020 02mar2020) to be 5 (instead of 7), and so on. Any further suggestions? Thanks a lot.

[Nick Cox]

So now the key date is 24 February. You may get closer with this. Is there always a 24 February observation?

Perhaps this is a round-about work-around for not having a proper business calendar!

bysort year (ymd) : gen base = sum(_n * (ymd == mdy(2, 24, year))) 
by year : replace base = base[_N]
by year : gen wanted = base - _n 

list if inrange(wanted, -5, 5), sepby(year)

     +----------------------------------------------+
     | year         ymd           x   base   wanted |
     |----------------------------------------------|
 34. | 2020   17feb2020   2.8824496     39        5 |
 35. | 2020   18feb2020   2.9022966     39        4 |
 36. | 2020   19feb2020   2.9767756     39        3 |
 37. | 2020   20feb2020   2.9804515     39        2 |
 38. | 2020   21feb2020    2.972797     39        1 |
 39. | 2020   24feb2020   2.9196073     39        0 |
 40. | 2020   25feb2020   2.9311152     39       -1 |
 41. | 2020   26feb2020   2.9755143     39       -2 |
 42. | 2020   27feb2020   2.9541645     39       -3 |
 43. | 2020   28feb2020   2.7817184     39       -4 |
 44. | 2020   02mar2020   2.8538843     39       -5 |
     |----------------------------------------------|
296. | 2021   17feb2021   5.7902255     39        5 |
297. | 2021   18feb2021   6.1553313     39        4 |
298. | 2021   19feb2021    5.899855     39        3 |
299. | 2021   22feb2021   5.6294948     39        2 |
300. | 2021   23feb2021   5.7374626     39        1 |
301. | 2021   24feb2021   5.8223439     39        0 |
302. | 2021   25feb2021   5.6462033     39       -1 |
303. | 2021   26feb2021   5.5434975     39       -2 |
304. | 2021   01mar2021   5.6977518     39       -3 |
305. | 2021   02mar2021   5.6524717     39       -4 |
306. | 2021   03mar2021   5.4124754     39       -5 |
     +----------------------------------------------+

[River Huang]

Dear Nick, 1. Yes, for my current 3-year data set, there is always a 24 February observation (but I am not sure if I extend my data to more years). 2. However, I want

by year : gen wanted = _n - base

Thanks a lot for this suggestion.

[Nick Cox]

Sorry, yes; that's the right way round.