Keep data around dates in two variables

[Lisa Na]

Hello, I have been searching for a while for a solution to my question, but can't find an answer.

I have a dataset of companies paying dividends, with in the dataset the name of the company, the announcement date and the ex-dividend date. Now I would like to keep the dates of observations that are, for example, 1 day before the announcement date and 1 day after the ex dividend date. For example, in these dates this is from 18301 to 18311. But between the announcement date and ex dividend date there are not always the same number of days, so it is not possible to take a fixed range of say (-1,+6) for this first value. So the command must depend on the announcement date and the ex dividend date e.g. keep from announcement date -1 to ex dividend date +1.

Is there a solution for this?


* Example generated by -dataex-. For more info, type help dataex
clear
input long Date str36 Company long(DCLRDT Ex_div)
18266 "3M CO" . .
18267 "3M CO" . .
18268 "3M CO" . .
18269 "3M CO" . .
18270 "3M CO" . .
18273 "3M CO" . .
18274 "3M CO" . .
18275 "3M CO" . .
18276 "3M CO" . .
18277 "3M CO" . .
18281 "3M CO" . .
18282 "3M CO" . .
18283 "3M CO" . .
18284 "3M CO" . .
18287 "3M CO" . .
18288 "3M CO" . .
18289 "3M CO" . .
18290 "3M CO" . .
18291 "3M CO" . .
18294 "3M CO" . .
18295 "3M CO" . .
18296 "3M CO" . .
18297 "3M CO" . .
18298 "3M CO" . .
18301 "3M CO" . .
18302 "3M CO" 18302 .
18303 "3M CO" . .
18304 "3M CO" . .
18305 "3M CO" . .
18309 "3M CO" . .
18310 "3M CO" . 18310
18311 "3M CO" . .
18312 "3M CO" . .
18315 "3M CO" . .
18316 "3M CO" . .
18317 "3M CO" . .
18318 "3M CO" . .
18319 "3M CO" . .
18322 "3M CO" . .
18323 "3M CO" . .
18324 "3M CO" . .
18325 "3M CO" . .
18326 "3M CO" . .
18329 "3M CO" . .
18330 "3M CO" . .
18331 "3M CO" . .
18332 "3M CO" . .
18333 "3M CO" . .
18336 "3M CO" . .
18337 "3M CO" . .
18338 "3M CO" . .
18339 "3M CO" . .
18340 "3M CO" . .
18343 "3M CO" . .
18344 "3M CO" . .
18345 "3M CO" . .
18346 "3M CO" . .
18347 "3M CO" . .
18350 "3M CO" . .
18351 "3M CO" . .
18352 "3M CO" . .
18353 "3M CO" . .
18357 "3M CO" . .
18358 "3M CO" . .
18359 "3M CO" . .
18360 "3M CO" . .
18361 "3M CO" . .
18364 "3M CO" . .
18365 "3M CO" . .
18366 "3M CO" . .
18367 "3M CO" . .
18368 "3M CO" . .
18371 "3M CO" . .
18372 "3M CO" . .
18373 "3M CO" . .
18374 "3M CO" . .
18375 "3M CO" . .
18378 "3M CO" . .
18379 "3M CO" . .
18380 "3M CO" . .
18381 "3M CO" . .
18382 "3M CO" . .
18385 "3M CO" . .
18386 "3M CO" . .
18387 "3M CO" . .
18388 "3M CO" . .
18389 "3M CO" . .
18392 "3M CO" . .
18393 "3M CO" 18393 .
18394 "3M CO" . .
18395 "3M CO" . .
18396 "3M CO" . .
18399 "3M CO" . .
18400 "3M CO" . .
18401 "3M CO" . 18401
18402 "3M CO" . .
18403 "3M CO" . .
18406 "3M CO" . .
18407 "3M CO" . .
18408 "3M CO" . .
end
format %td Date
format %td DCLRDT
format %td Ex_div
[/CODE]

[Clyde Schechter]

I notice that the sequence of the Date variable has gaps (that I guess reflect weekends and holidays or other such). So when you say 1 day before/after the announcement/ex-dividend date, do you mean the day immediately before/after in the data set (which might be more than 1 calendar day away), or do you literally mean 1 single day?

Also, in your example data, each announcement date is followed by an ex-dividend date with no additional announcement dates in between. Is that always the case, or can we have a situation where a second announcement follows an announcement but precedes the next ex-dividend date? If the latter situation can arise, how do you wish to handle it?

And thank you for using -dataex- on your very first post.

[Lisa Na]

By 1 day before or after I mean 1 trading day, in other words 1 day in the data set (i.e. no weekends/holidays).

In most cases it is indeed announcement day and the next date is then the ex-dividend date. Very rarely it happens that there are 2 announcement dates one after the other and only then the ex-dividend date. In such a case, I would opt for taking the date closest to the ex-dividend date and thus ignoring or removing the first announcement date.

[Clyde Schechter]

OK. I have modified your -dataex- example to include a situation where there are two announcement dates preceding an ex-dividend event.

* Example generated by -dataex-. For more info, type help dataex
clear
input long Date str36 Company long(DCLRDT Ex_div)
18266 "3M CO" . .
18267 "3M CO" . .
18268 "3M CO" . .
18269 "3M CO" . .
18270 "3M CO" . .
18273 "3M CO" . .
18274 "3M CO" . .
18275 "3M CO" . .
18276 "3M CO" . .
18277 "3M CO" . .
18281 "3M CO" . .
18282 "3M CO" . .
18283 "3M CO" . .
18284 "3M CO" . .
18287 "3M CO" . .
18288 "3M CO" . .
18289 "3M CO" . .
18290 "3M CO" . .
18291 "3M CO" . .
18294 "3M CO" . .
18295 "3M CO" . .
18296 "3M CO" . .
18297 "3M CO" . .
18298 "3M CO" . .
18301 "3M CO" . .
18302 "3M CO" 18302 .
18303 "3M CO" . .
18304 "3M CO" . .
18305 "3M CO" . .
18309 "3M CO" . .
18310 "3M CO" . 18310
18311 "3M CO" . .
18312 "3M CO" . .
18315 "3M CO" . .
18316 "3M CO" . .
18317 "3M CO" . .
18318 "3M CO" . .
18319 "3M CO" . .
18322 "3M CO" . .
18323 "3M CO" . .
18324 "3M CO" . .
18325 "3M CO" . .
18326 "3M CO" . .
18329 "3M CO" . .
18330 "3M CO" . .
18331 "3M CO" . .
18332 "3M CO" . .
18333 "3M CO" . .
18336 "3M CO" . .
18337 "3M CO" . .
18338 "3M CO" . .
18339 "3M CO" . .
18340 "3M CO" . .
18343 "3M CO" . .
18344 "3M CO" . .
18345 "3M CO" . .
18346 "3M CO" . .
18347 "3M CO" . .
18350 "3M CO" . .
18351 "3M CO" . .
18352 "3M CO" . .
18353 "3M CO" . .
18357 "3M CO" . .
18358 "3M CO" . .
18359 "3M CO" . .
18360 "3M CO" . .
18361 "3M CO" . .
18364 "3M CO" . .
18365 "3M CO" . .
18366 "3M CO" . .
18367 "3M CO" . .
18368 "3M CO" . .
18371 "3M CO" . .
18372 "3M CO" . .
18373 "3M CO" . .
18374 "3M CO" . .
18375 "3M CO" . .
18378 "3M CO" . .
18379 "3M CO" . .
18380 "3M CO" . .
18381 "3M CO" . .
18382 "3M CO" . .
18385 "3M CO" . .
18386 "3M CO" . .
18387 "3M CO" . .
18388 "3M CO" . .
18389 "3M CO" . .
18392 "3M CO" . .
18393 "3M CO" 18393 .
18394 "3M CO" . .
18395 "3M CO" 18395 .
18396 "3M CO" . .
18399 "3M CO" . .
18400 "3M CO" . .
18401 "3M CO" . 18401
18402 "3M CO" . .
18403 "3M CO" . .
18406 "3M CO" . .
18407 "3M CO" . .
18408 "3M CO" . .
end
format %td Date
format %td DCLRDT
format %td Ex_div

isid Company Date
gsort Company -Date
by Company: gen state = !missing(Ex_div[1], Ex_div[2]) if _n == 1
by Company: replace state = ///
    cond(state[_n-1] == 0, !missing(Ex_div[_n+1]), ///
        cond(!missing(DCLRDT[_n-2]), 0, state[_n-1])) if _n > 1
keep if state
drop state
sort Company Date

Yes, the code is rather difficult to follow. If there is a simpler way to do it, I couldn't find it out. The complication arises from the possibility of multiple announcement dates and keeping only the latest among them.

[Lisa Na]

Thank you verry much for the help.