generate event time.

[River Huang]

Dear All, Soppose I have the following data (a related thread is here):

* Example generated by -dataex-. For more info, type help dataex
clear
input int ymd double wti float(year base etime)
21216  65.8 2018  0   1
21217 65.45 2018  0   2
21220 64.15 2018  0   3
21221 63.39 2018  0   4
21222 61.79 2018  0   5
21223 61.15 2018  0   6
21224  59.2 2018  0   7
21227 59.29 2018  0   8
21228 59.19 2018  0   9
21229  60.6 2018  0  10
21230 61.34 2018  0  11
21231 61.68 2018  0  12
21235  61.9 2018  0  13
21236 61.68 2018  0  14
21237 62.77 2018  0  15
21238 63.55 2018  0  16
21241 63.91 2018  0  17
21242 63.01 2018  0  18
21243 61.64 2018  0  19
21244 60.99 2018  0  20
21245 61.25 2018  0  21
21248 62.57 2018  0  22
21249  62.6 2018  0  23
21250 61.15 2018  0  24
21251 60.12 2018  0  25
21252 62.04 2018  0  26
21255 61.36 2018  0  27
21256 60.71 2018  0  28
21257 60.96 2018  0  29
21258 61.19 2018  0  30
21259 62.34 2018  0  31
21262 62.06 2018  0  32
21263  63.4 2018  0  33
21264 65.17 2018  0  34
21265  64.3 2018  0  35
21266 65.88 2018  0  36
21269 65.55 2018  0  37
21270 65.25 2018  0  38
21271 64.38 2018  0  39
21272 64.94 2018  0  40
21581 55.26 2019  0   1
21584 54.56 2019  0   2
21585 53.66 2019  0   3
21586 54.01 2019  0   4
21587 52.64 2019  0   5
21588 52.72 2019  0   6
21591 52.41 2019  0   7
21592  53.1 2019  0   8
21593  53.9 2019  0   9
21594 54.41 2019  0  10
21595 55.59 2019  0  11
21599 56.09 2019  0  12
21600 56.92 2019  0  13
21601 56.96 2019  0  14
21602 57.26 2019  0  15
21605 55.48 2019  0  16
21606  55.5 2019  0  17
21607 56.94 2019  0  18
21608 57.22 2019  0  19
21609  55.8 2019  0  20
21612 56.59 2019  0  21
21613 56.56 2019  0  22
21614 56.22 2019  0  23
21615 56.66 2019  0  24
21616 56.07 2019  0  25
21619 56.79 2019  0  26
21620 56.87 2019  0  27
21621 58.26 2019  0  28
21622 58.61 2019  0  29
21623 58.52 2019  0  30
21626 59.09 2019  0  31
21627 59.03 2019  0  32
21628 59.83 2019  0  33
21629 59.98 2019  0  34
21630 59.04 2019  0  35
21633 58.82 2019  0  36
21634 59.94 2019  0  37
21635 59.41 2019  0  38
21636  59.3 2019  0  39
21637 60.14 2019  0  40
21948 50.11 2020 15 -14
21949 49.61 2020 15 -13
21950 50.75 2020 15 -12
21951 50.95 2020 15 -11
21952 50.32 2020 15 -10
21955 49.57 2020 15  -9
21956 49.94 2020 15  -8
21957 51.17 2020 15  -7
21958 51.42 2020 15  -6
21959 52.05 2020 15  -5
21963 52.05 2020 15  -4
21964 53.29 2020 15  -3
21965 53.78 2020 15  -2
21966 53.38 2020 15  -1
21969 51.43 2020 15   0
21970  49.9 2020 15   1
21971 48.73 2020 15   2
21972 47.09 2020 15   3
21973 44.76 2020 15   4
21976 46.75 2020 15   5
end
format %tdnn/dd/CCYY ymd

I have trading data (on daily prices) for three years (shown only about 90 observations per year here), where `ymd" and "year" are dates and years, and `wti' is the prices. I used (suggested by Nick Cox) the following code

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

to generate `etime' (for event time), which is equal to 0 on February 24 (for every year), and previous trading day (i.e., with data on `wti') being -1, and next trading day being 1, and so on (please see `etime' in year 2020 for what I want.). However, in this new case, there were no trades on February 24 in year 2019, and 2018. In such case, how can I use the most neighboring trade day to set `etime'=0. For instance, in year 2018, I set Febrary 23 to be etime=0, and in year 2019, I set Febrary 25 to be etime=0. Thanks.

[Hemanshu Kumar]

Here's some code that should do it:

sort year ymd
gen gap = abs(ymd - mdy(2,24,year))
by year: egen min_gap = min(gap)
by year: egen byte base2 = max(cond(gap==min_gap,_n,.)) // tie-breaker picks later date if max() is used, and earlier date if min() is used
by year: gen etime2 = _n - base2
drop gap min_gap

[Nick Cox]

I had this code. I haven't tested this or the code of Hemanshu Kumar

gen diff = abs(ymd - mdy(2, 24, year))
bysort year (diff) : gen refdate = ymd[1]
bysort year (ymd) : gen base = sum(_n * (ymd == refdate))

If you have 2 days equally distant from 24 February you might want to refine the choice.

[River Huang]

Dear Hemanshu, Thanks a lot for this helpful suggestion.

[River Huang]

Dear Nick, Thanks a lot for this helpful suggestion.

[River Huang]

Dear Hemanshu, I wonder if you can help me with the following question. Suppose my (trading) data is

* Example generated by -dataex-. For more info, type help dataex
clear
input float year int ymd double brent
2022 22826 114.81
2022 22825 116.26
2022 22824 117.98
2022 22823 115.09
2022 22820 113.12
2022 22819 110.05
2022 22818 111.74
2022 22817 114.65
2022 22813 113.12
2022 22812 119.81
2022 22811 118.51
2022 22810 121.17
2022 22809 122.27
2022 22806 122.01
2022 22805 123.07
2022 22804 123.58
2022 22803 120.57
2022 22802 119.51
2022 22799 119.72
2022 22798 117.61
2022 22797 116.29
2022 22796 122.84
2022 22792 119.43
2022 22791  117.4
2022 22790 114.03
2022 22789 113.56
2022 22788 113.42
2022 22785 112.55
2022 22784 112.04
2022 22783 109.11
2022 22782 111.93
2022 22781 114.24
2022 22778 111.55
2022 22777 107.45
2022 22776 107.51
2022 22775 102.46
2022 22774 105.94
2022 22771 112.39
2022 22770  110.9
2022 22769 110.14
2022 22768 104.97
2022 22767 107.58
2022 22764 109.34
2022 22763 107.59
2022 22762 105.32
2022 22761 104.99
2022 22760 102.32
2022 22757 106.65
2022 22756 108.33
2022 22755  106.8
2022 22754 107.25
2022 22753 113.16
2022 22749  111.7
2022 22748 108.78
2022 22747 104.64
2022 22746  98.48
2022 22743 102.78
2022 22742 100.58
2022 22741 101.07
2022 22740 106.64
2022 22739 107.53
2022 22736 104.39
2022 22735 107.91
2022 22734 113.45
2022 22733 110.23
2022 22732 112.48
2022 22729 120.65
2022 22728 119.03
2022 22727  121.6
2022 22726 115.48
2022 22725 115.62
2022 22722 107.93
2022 22721 106.64
2022 22720  98.02
2022 22719  99.91
2022 22718  106.9
2022 22715 112.67
2022 22714 109.33
2022 22713 111.14
2022 22712 127.98
2022 22711 123.21
2022 22708 118.11
2022 22707 110.46
2022 22706 112.93
2022 22705 104.97
2022 22704 100.99
2022 22701  97.93
2022 22700  99.08
2022 22699  96.84
2022 22698  96.84
2022 22694  93.54
2022 22693  92.97
2022 22692  94.81
2022 22691  93.28
2022 22690  96.48
2022 22687  94.44
2022 22686  91.41
2022 22685  91.55
2022 22684  90.78
2022 22683  92.69
2022 22680  93.27
2022 22679  91.11
2022 22678  89.47
2022 22677  89.16
2022 22676  91.21
2022 22673  90.03
2022 22672  89.34
2022 22671  89.96
2022 22670   88.2
2022 22669  86.27
2022 22666  87.89
2022 22665  88.38
2022 22664  88.44
2022 22663  87.51
2022 22659  86.06
2022 22658  84.47
2022 22657  84.67
2022 22656  83.72
2022 22655  80.87
2022 22652  81.75
2022 22651  81.99
2022 22650   80.8
2022 22649     80
2022 22648  78.98
2021 22645  77.78
2021 22644  79.32
2021 22643  79.23
2021 22642  78.94
2021 22641   78.6
2021 22637  76.85
2021 22636  75.29
2021 22635  73.98
2021 22634  71.52
2021 22631  73.52
2021 22630  75.02
2021 22629  73.88
2021 22628   73.7
2021 22627  74.39
2021 22624  75.15
2021 22623  74.42
2021 22622  75.82
2021 22621  75.44
2021 22620  73.08
2021 22617  69.88
2021 22616  69.67
2021 22615  68.87
2021 22614  70.57
2021 22613  73.44
2021 22610  72.72
2021 22608  82.25
2021 22607  82.31
2021 22606   79.7
2021 22603  78.89
2021 22602  81.24
2021 22601  80.28
2021 22600  82.43
2021 22599  82.05
2021 22596  82.17
2021 22595  82.87
2021 22594  82.64
2021 22593  84.78
2021 22592  83.43
2021 22589  82.74
2021 22588  80.54
2021 22587  81.99
2021 22586  84.72
2021 22585  84.71
2021 22582  84.38
2021 22581  84.32
2021 22580  84.58
2021 22579   86.4
2021 22578  85.99
2021 22575  85.53
2021 22574  84.61
2021 22573  85.82
2021 22572  85.08
2021 22571  84.33
2021 22568  84.86
2021 22567     84
2021 22566  83.18
2021 22565  83.42
2021 22564  83.65
2021 22561  82.39
2021 22560  81.95
2021 22559  81.08
2021 22558  82.56
2021 22557  81.26
2021 22554  79.28
2021 22553  78.52
2021 22552  78.64
2021 22551  79.09
2021 22550  79.53
2021 22547  78.09
2021 22546  77.25
2021 22545  76.19
2021 22544  74.36
2021 22543  73.92
2021 22540  75.34
2021 22539  75.67
2021 22538  75.46
2021 22537   73.6
2021 22536  73.51
2021 22533  72.92
2021 22532  71.45
2021 22531   72.6
2021 22530  71.69
2021 22526  72.61
2021 22525  73.03
2021 22524  71.59
2021 22523  72.99
2021 22522  73.41
2021 22519   72.7
2021 22518  71.07
2021 22517  72.25
2021 22516  71.05
2021 22515  68.75
2021 22512  65.18
2021 22511  66.45
2021 22510  68.23
2021 22509  69.03
2021 22508  69.51
2021 22505  70.59
2021 22504  71.31
2021 22503  71.44
2021 22502  70.63
2021 22501  69.04
2021 22498   70.7
2021 22497  71.29
2021 22496  70.38
2021 22495  72.41
2021 22494  72.89
2021 22491  76.33
2021 22490  76.05
2021 22489  74.74
2021 22488  74.48
2021 22487   74.5
2021 22484   74.1
2021 22483  73.79
2021 22482  72.23
2021 22481  69.35
2021 22480  68.62
2021 22477  73.59
2021 22476  73.47
2021 22475  74.76
2021 22474  76.49
2021 22473  75.16
2021 22470  75.55
2021 22469  74.12
2021 22468  73.43
2021 22467  74.53
2021 22463  76.17
2021 22462  75.84
2021 22461  75.13
2021 22460  74.76
2021 22459  74.68
2021 22456  76.18
2021 22455  75.56
2021 22454  75.19
2021 22453  74.81
2021 22452   74.9
2021 22449  73.51
2021 22448  73.08
2021 22447  74.39
2021 22446  73.99
2021 22445  72.86
2021 22442  72.69
2021 22441  72.52
2021 22440  72.22
2021 22439  72.22
2021 22438  71.49
2021 22435  71.89
2021 22434  71.31
2021 22433  71.35
2021 22432  70.25
2021 22428  69.63
2021 22427  69.46
2021 22426  68.87
2021 22425  68.65
2021 22424  68.46
2021 22421  66.44
2021 22420  65.11
2021 22419  66.66
2021 22418  68.71
2021 22417  69.46
2021 22414  68.71
2021 22413  67.05
2021 22412  69.32
2021 22411  68.55
2021 22410  68.32
2021 22407  68.28
2021 22406  68.09
2021 22405  68.96
2021 22404  68.88
2021 22403  67.56
2021 22400  67.25
2021 22399  68.56
2021 22398  67.27
2021 22397  66.42
2021 22396  65.65
2021 22393  66.11
2021 22392   65.4
2021 22391  65.32
2021 22390  66.57
2021 22389  67.05
2021 22386  66.77
2021 22385  66.94
2021 22384  66.58
2021 22383  63.67
2021 22382  63.28
2021 22379  62.95
2021 22378   63.2
2021 22377  63.16
2021 22376  62.74
2021 22375  62.15
2021 22371  64.86
2021 22370  63.54
2021 22369  64.14
2021 22368  64.98
2021 22365  64.57
2021 22364  61.95
2021 22363  64.41
2021 22362  60.79
2021 22361  64.62
2021 22358  64.53
2021 22357  63.28
2021 22356     68
2021 22355  68.39
2021 22354  68.88
2021 22351  69.22
2021 22350  69.63
2021 22349   67.9
2021 22348  67.52
2021 22347  68.24
2021 22344  69.36
2021 22343  66.74
2021 22342  64.07
2021 22341   62.7
2021 22340  63.69
2021 22337  66.13
2021 22336  66.88
2021 22335  67.04
2021 22334  65.37
2021 22333  65.24
2021 22330  62.91
2021 22329  63.93
2021 22328  64.34
2021 22327  63.35
2021 22323  62.43
2021 22322  61.14
2021 22321  61.47
2021 22320  61.09
2021 22319  60.56
2021 22316  59.34
2021 22315  58.84
2021 22314  58.46
2021 22313  57.46
2021 22312  56.35
2021 22309  55.88
2021 22308  55.53
2021 22307  55.81
2021 22306  55.91
2021 22305  55.88
2021 22302  55.41
2021 22301   56.1
2021 22300  56.08
2021 22299   55.9
2021 22295   55.1
2021 22294  56.42
2021 22293  56.06
2021 22292  56.58
2021 22291  55.66
2021 22288  55.99
2021 22287  54.38
2021 22286   54.3
2021 22285   53.6
2021 22284  51.09
2020 22280   51.8
2020 22279  51.34
2020 22278  51.09
2020 22277  50.86
2020 22273  51.29
2020 22272   51.2
2020 22271  50.08
2020 22270  50.91
2020 22267  52.26
2020 22266   51.5
2020 22265  51.08
2020 22264  50.76
2020 22263  50.29
2020 22260  49.97
2020 22259  50.25
2020 22258  48.86
2020 22257  48.84
2020 22256  48.79
2020 22253  49.25
2020 22252  48.71
2020 22251  48.25
2020 22250  47.42
2020 22249  47.59
2020 22246  48.18
2020 22244  48.61
2020 22243  47.86
2020 22242  46.06
2020 22239  44.96
2020 22238   44.2
2020 22237  44.34
2020 22236  43.75
2020 22235  43.82
2020 22232  42.78
2020 22231  43.53
2020 22230   43.8
2020 22229  43.61
2020 22228   42.4
2020 22225  39.45
2020 22224  40.93
2020 22223  41.23
2020 22222  39.71
2020 22221  38.97
2020 22218  37.46
2020 22217  37.65
2020 22216  39.12
2020 22215   41.2
2020 22214  40.46
2020 22211  41.77
2020 22210  42.46
2020 22209  41.73
2020 22208  43.16
2020 22207  42.62
2020 22204  42.93
2020 22203  43.16
2020 22202  43.32
2020 22201  42.45
2020 22200  41.72
2020 22197  42.85
2020 22196  43.34
2020 22195  41.99
2020 22194  42.65
2020 22193  41.29
2020 22190  39.27
2020 22189  40.93
2020 22188  40.95
2020 22187  41.03
2020 22186  42.43
2020 22183  41.92
2020 22182  41.94
2020 22181  41.77
2020 22180  41.72
2020 22179  41.44
2020 22176  43.15
2020 22175   43.3
2020 22174  42.22
2020 22173  40.53
2020 22172  39.61
2020 22169  39.83
2020 22168  40.06
2020 22167  40.79
2020 22166  39.78
2020 22162  42.66
2020 22161  44.07
2020 22160  44.43
2020 22159  45.58
2020 22158  45.28
2020 22155  45.05
2020 22154  45.09
2020 22153  45.64
2020 22152  45.86
2020 22151  45.13
2020 22148  44.35
2020 22147   44.9
2020 22146  45.37
2020 22145  45.46
2020 22144  45.37
2020 22141   44.8
2020 22140  44.96
2020 22139  45.43
2020 22138   44.5
2020 22137  44.99
2020 22134   44.4
2020 22133  45.09
2020 22132  45.17
2020 22131  44.43
2020 22130  44.15
2020 22127   43.3
2020 22126  42.94
2020 22125  43.75
2020 22124  43.22
2020 22123  43.41
2020 22120  43.34
2020 22119  43.31
2020 22118  44.29
2020 22117  44.32
2020 22116  43.28
2020 22113  43.14
2020 22112  43.37
2020 22111  43.79
2020 22110   42.9
2020 22109  42.72
2020 22106  43.24
2020 22105  42.35
2020 22104  43.29
2020 22103  43.08
2020 22102   43.1
2020 22098  43.14
2020 22097  42.03
2020 22096  41.15
2020 22095  41.71
2020 22092  41.02
2020 22091  41.05
2020 22090  40.31
2020 22089  42.63
2020 22088  43.08
2020 22085  42.19
2020 22084  41.51
2020 22083  40.71
2020 22082  40.96
2020 22081  39.72
2020 22078  38.73
2020 22077  38.55
2020 22076  41.73
2020 22075  41.18
2020 22074   40.8
2020 22071   42.3
2020 22070  39.99
2020 22069  39.79
2020 22068  39.57
2020 22067  38.32
2020 22064  35.33
2020 22063  35.29
2020 22062  34.74
2020 22061  36.17
2020 22057  35.13
2020 22056  36.06
2020 22055  35.75
2020 22054  34.65
2020 22053  34.81
2020 22050   32.5
2020 22049  31.13
2020 22048  29.19
2020 22047  29.98
2020 22046  29.63
2020 22043  30.97
2020 22042  29.46
2020 22041  29.72
2020 22040  30.97
2020 22039   27.2
2020 22036  26.44
2020 22035  25.27
2020 22034  22.54
2020 22033  20.46
2020 22032  19.99
2020 22029  21.44
2020 22028  21.33
2020 22027  20.37
2020 22026  19.33
2020 22025  25.57
2020 22022  28.08
2020 22021  27.82
2020 22020  27.69
2020 22019   29.6
2020 22018  31.74
2020 22014  31.48
2020 22013  32.84
2020 22012  31.87
2020 22011  33.05
2020 22008  34.11
2020 22007  29.94
2020 22006  24.74
2020 22005  22.74
2020 22004  22.76
2020 22001  24.93
2020 22000  26.34
2020 21999  27.39
2020 21998  27.15
2020 21997  27.03
2020 21994  26.98
2020 21993  28.47
2020 21992  24.88
2020 21991  28.73
2020 21990  30.05
2020 21987  33.85
2020 21986  33.22
2020 21985  35.79
2020 21984  37.22
2020 21983  34.36
2020 21980  45.27
2020 21979  49.99
2020 21978  51.13
2020 21977  51.86
2020 21976   51.9
2020 21973  50.52
2020 21972  52.18
2020 21971  53.43
2020 21970  54.95
2020 21969   56.3
2020 21966   58.5
2020 21965  59.31
2020 21964  59.12
2020 21963  57.75
2020 21959  57.32
2020 21958  56.34
2020 21957  55.79
2020 21956  54.01
2020 21955  53.27
2020 21952  54.47
2020 21951  54.93
2020 21950  55.28
2020 21949  53.96
2020 21948  54.45
2020 21945  58.16
2020 21944  58.29
2020 21943  59.81
2020 21942  59.51
2020 21941  59.32
2020 21938  60.69
2020 21937  62.04
2020 21936  63.21
2020 21935  64.59
2020 21931  64.85
2020 21930  64.62
2020 21929     64
2020 21928  64.49
2020 21927   64.2
2020 21924  64.98
2020 21923  65.37
2020 21922  65.44
2020 21921  68.27
2020 21920  68.91
2020 21917   68.6
2020 21916  66.25
2019 21914     66
2019 21913  68.44
2019 21910  68.16
2019 21909  67.92
2019 21907   67.2
2019 21906  66.39
2019 21903  66.14
2019 21902  66.54
2019 21901  66.17
2019 21900   66.1
2019 21899  65.34
2019 21896  65.22
2019 21895   64.2
2019 21894  63.72
2019 21893  64.34
2019 21892  64.25
2019 21889  64.39
2019 21888  63.39
2019 21887     63
2019 21886  60.82
2019 21885  60.92
2019 21882  62.43
2019 21880  64.06
2019 21879  64.27
2019 21878  63.65
2019 21875  63.39
2019 21874  63.97
2019 21873   62.4
2019 21872  60.91
2019 21871  62.44
2019 21868   63.3
2019 21867  62.28
2019 21866  62.37
2019 21865  62.06
2019 21864  62.18
2019 21861  62.51
2019 21860  62.29
2019 21859  61.74
2019 21858  62.96
2019 21857  62.13
2019 21854  61.69
2019 21853  60.23
2019 21852  60.61
2019 21851  61.59
2019 21850  61.57
2019 21847  62.02
2019 21846  61.67
2019 21845  61.17
2019 21844   59.7
2019 21843  58.96
2019 21840  59.42
2019 21839  59.91
2019 21838  59.42
2019 21837  58.74
2019 21836  59.35
2019 21833  60.51
2019 21832   59.1
2019 21831  58.32
2019 21830  58.24
2019 21829  58.35
2019 21826  58.37
2019 21825  57.71
2019 21824  57.69
2019 21823  58.89
2019 21822  60.78
2019 21819  61.91
2019 21818  62.74
2019 21817  62.39
2019 21816   63.1
2019 21815  64.77
2019 21812  64.28
2019 21811   64.4
2019 21810   63.6
2019 21809  64.55
2019 21808  69.02
2019 21805  60.22
2019 21804  60.38
2019 21803  60.81
2019 21802  62.38
2019 21801  62.59
2019 21798  61.54
2019 21797  60.95
2019 21796   60.7
2019 21795  58.26
2019 21791  60.43
2019 21790  61.08
2019 21789  60.49
2019 21788  59.51
2019 21787   58.7
2019 21784  59.34
2019 21783  59.92
2019 21782   60.3
2019 21781  60.03
2019 21780  59.74
2019 21777  58.64
2019 21776  58.23
2019 21775  59.48
2019 21774   61.3
2019 21773  58.57
2019 21770  58.53
2019 21769  57.38
2019 21768  56.23
2019 21767  58.94
2019 21766  59.81
2019 21763  61.89
2019 21762   60.5
2019 21761  65.17
2019 21760  64.72
2019 21759  63.71
2019 21756  63.46
2019 21755  63.39
2019 21754  63.18
2019 21753  63.83
2019 21752  63.26
2019 21749  62.47
2019 21748  61.93
2019 21747  63.66
2019 21746  64.35
2019 21745  66.48
2019 21742  66.72
2019 21741  66.52
2019 21740  67.01
2019 21739  64.16
2019 21738  64.11
2019 21735  64.23
2019 21733  63.82
2019 21732   62.4
2019 21731  65.06
end
format %tdnn/dd/CCYY ymd

I'd likw to generate a variable `etime' (event time), which is 0 on February 24 (or closest to) for every year. I wish to include 60 (trading) days before and after February 24 (so some observations will come from the previous year). Thus, etime = -60, -59, ..., -2, -1 before February 24, etime=1, 2,...,59, 60 after February 24. Thanks in advance.

[River Huang]

Dear Nick, Could you have a look on the latest question above? Thanks.