Hi again all. Sorry I keep sliding in here with super-specific panel data questions, and I swear I read/play around with as much as possible before posting!
So what I'm trying to do is group observations based on whether they came before a specific event on a given day (defined as 9am-8:59:59 in this case) by ID. In the example below, 'day' represents that interval (9am-8:59), obsdatetime is the date-time at which each observation occurred, and then eventdatetime is the time at which the event occurred. What I'm trying to do is generate a variable that assigns a 'period' identifier (e.g., 1, 2, 3) to observations by whether it: (a) came before the next event on a given 'day', or (2) if it never occurred that day, just the whole day. While this is easy for most days because an event never happened, some days two and three events happened, so I'm hoping to split those 'days' into multiple periods. Some events also happened at the same time, so these can just be included in the same period. And observations that come after an event can just be missing for this period identifier.
So far, I managed to tag each observation with the datetime of the next upcoming eventdatetime if there was one, or missing if there wasn't for a given day or if an observation came after the last event. I did that by:
So now I have this:
What I'm trying to achieve is this:
Any ideas?
So what I'm trying to do is group observations based on whether they came before a specific event on a given day (defined as 9am-8:59:59 in this case) by ID. In the example below, 'day' represents that interval (9am-8:59), obsdatetime is the date-time at which each observation occurred, and then eventdatetime is the time at which the event occurred. What I'm trying to do is generate a variable that assigns a 'period' identifier (e.g., 1, 2, 3) to observations by whether it: (a) came before the next event on a given 'day', or (2) if it never occurred that day, just the whole day. While this is easy for most days because an event never happened, some days two and three events happened, so I'm hoping to split those 'days' into multiple periods. Some events also happened at the same time, so these can just be included in the same period. And observations that come after an event can just be missing for this period identifier.
So far, I managed to tag each observation with the datetime of the next upcoming eventdatetime if there was one, or missing if there wasn't for a given day or if an observation came after the last event. I did that by:
Code:
gen double eventdatetime1=eventdatetime format eventdatetime1%tcNN/DD/CCYY_HH:MM:SS gsort id day -obsdatetime by id day: replace eventdatetime1 = eventdatetime1[_n-1] if missing(eventdatetime1)
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input double id float day double(obsdatetime eventdatetime eventdatetime1) float prd 119 1 1787159832000 . 1.7871678e+12 . 119 1 1787159956000 . 1.7871678e+12 . 119 1 1.7871678e+12 1.7871678e+12 1.7871678e+12 . 119 1 1.7871714e+12 1.7871714e+12 1.7871714e+12 . 119 1 1787177959000 . . . 119 2 1787217769000 . . . 119 2 1787226397000 . . . 119 2 1787234426000 . . . 119 2 1787243424000 . . . 119 2 1787253795000 . . . 119 2 1787262198000 . . . 119 3 1787303693000 . . . 119 3 1787303841000 . . . 119 3 1787321256000 . . . 119 3 1787330805000 . . . 119 3 1787339597000 . . . 119 3 1787349824000 . . . 119 4 1787389766000 . . . 119 4 1787396971000 . . . 119 4 1787406181000 . . . 119 4 1787424176000 . . . 119 5 1787475686000 . 1.7875314e+12 . 119 5 1787483042000 . 1.7875314e+12 . 119 5 1787491465000 . 1.7875314e+12 . 119 5 1787501112000 . 1.7875314e+12 . 119 5 1787510888000 . 1.7875314e+12 . 119 5 1787519871000 . 1.7875314e+12 . 119 5 1787527948000 . 1.7875314e+12 . 119 5 1.7875314e+12 1.7875314e+12 1.7875314e+12 . 119 6 1787568915000 . . . 119 6 1787579242000 . . . 119 6 1787587287000 . . . 119 6 1787597094000 . . . 119 6 1787614259000 . . . 119 7 1787649519000 . . . 119 7 1787649610000 . . . 119 7 1787659453000 . . . 119 7 1787667094000 . . . 119 7 1787676985000 . . . 119 7 1787686590000 . . . 119 7 1787696143000 . . . 119 8 1787734853000 . . . 119 8 1787746780000 . . . 119 8 1787755227000 . . . 119 8 1787764532000 . . . 119 8 1787784143000 . . . 119 9 1787824901000 . . . 119 9 1787825617000 . . . 119 9 1787834391000 . . . 119 9 1787844798000 . . . 119 9 1787852852000 . . . 119 9 1787864144000 . . . 119 9 1787870962000 . . . 119 10 1787907704000 . 1.7879688e+12 . 119 10 1787909229000 . 1.7879688e+12 . 119 10 1787919159000 . 1.7879688e+12 . 119 10 1787927995000 . 1.7879688e+12 . 119 10 1787936517000 . 1.7879688e+12 . 119 10 1787946781000 . 1.7879688e+12 . 119 10 1787954588000 . 1.7879688e+12 . 119 10 1.7879688e+12 1.7879688e+12 1.7879688e+12 . 119 11 1787997443000 . . . 119 11 1788005989000 . . . 119 11 1788015805000 . . . 119 11 1788024247000 . . . 119 11 1788033675000 . . . 119 11 1788042563000 . . . 119 12 1788080534000 . . . 119 12 1788086743000 . . . 119 12 1788095057000 . . . 119 12 1788104028000 . . . 119 12 1788114686000 . . . 119 12 1788132825000 . . . 119 13 1788167968000 . . . 119 13 1788168185000 . . . 119 13 1788176805000 . . . 119 13 1788185648000 . . . 119 13 1788193744000 . . . 119 13 1788204063000 . . . 119 13 1788212818000 . . . 119 14 1788258757000 . . . 119 14 1788259339000 . . . 119 14 1788267816000 . . . 119 14 1788284663000 . . . 119 14 1788303682000 . . . 119 15 1788348652000 . . . 119 15 1788349822000 . . . 119 15 1788355849000 . . . 119 15 1788366820000 . . . 119 15 1788368462000 . . . 119 15 1788374117000 . . . 119 15 1.7883841e+12 . . . 119 15 1788391929000 . . . 119 16 1788433750000 . . . 119 16 1788436381000 . . . 119 16 1788444253000 . . . 119 16 1788453923000 . . . 119 16 1788461807000 . . . 119 16 1788472989000 . . . 119 16 1788479894000 . . . end format %tcNN/DD/CCYY_HH:MM:SS obsdatetime format %tcNN/DD/CCYY_HH:MM:SS eventdatetime format %tcNN/DD/CCYY_HH:MM:SS eventdatetime1
What I'm trying to achieve is this:
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input double id float day double(obsdatetime eventdatetime eventdatetime1) float prd 119 1 1787159832000 . 1.7871678e+12 1 119 1 1787159956000 . 1.7871678e+12 1 119 1 1.7871678e+12 1.7871678e+12 1.7871678e+12 1 119 1 1.7871714e+12 1.7871714e+12 1.7871714e+12 2 119 1 1787177959000 . . . 119 2 1787217769000 . . 3 119 2 1787226397000 . . 3 119 2 1787234426000 . . 3 119 2 1787243424000 . . 3 119 2 1787253795000 . . 3 119 2 1787262198000 . . 3 119 3 1787303693000 . . 4 119 3 1787303841000 . . 4 119 3 1787321256000 . . 4 119 3 1787330805000 . . 4 119 3 1787339597000 . . 4 119 3 1787349824000 . . 4 119 4 1787389766000 . . 5 119 4 1787396971000 . . 5 119 4 1787406181000 . . 5 119 4 1787424176000 . . 5 119 5 1787475686000 . 1.7875314e+12 6 119 5 1787483042000 . 1.7875314e+12 6 119 5 1787491465000 . 1.7875314e+12 6 119 5 1787501112000 . 1.7875314e+12 6 119 5 1787510888000 . 1.7875314e+12 6 119 5 1787519871000 . 1.7875314e+12 6 119 5 1787527948000 . 1.7875314e+12 6 119 5 1.7875314e+12 1.7875314e+12 1.7875314e+12 6 119 6 1787568915000 . . 7 119 6 1787579242000 . . 7 119 6 1787587287000 . . 7 119 6 1787597094000 . . 7 119 6 1787614259000 . . 7 119 7 1787649519000 . . 8 119 7 1787649610000 . . 8 119 7 1787659453000 . . 8 119 7 1787667094000 . . 8 119 7 1787676985000 . . 8 119 7 1787686590000 . . 8 119 7 1787696143000 . . 8 119 8 1787734853000 . . 9 119 8 1787746780000 . . 9 119 8 1787755227000 . . 9 119 8 1787764532000 . . 9 119 8 1787784143000 . . 9 119 9 1787824901000 . . 10 119 9 1787825617000 . . 10 119 9 1787834391000 . . 10 119 9 1787844798000 . . 10 119 9 1787852852000 . . 10 119 9 1787864144000 . . 10 119 9 1787870962000 . . 10 119 10 1787907704000 . 1.7879688e+12 11 119 10 1787909229000 . 1.7879688e+12 11 119 10 1787919159000 . 1.7879688e+12 11 119 10 1787927995000 . 1.7879688e+12 11 119 10 1787936517000 . 1.7879688e+12 11 119 10 1787946781000 . 1.7879688e+12 11 119 10 1787954588000 . 1.7879688e+12 11 119 10 1.7879688e+12 1.7879688e+12 1.7879688e+12 11 119 11 1787997443000 . . 12 119 11 1788005989000 . . 12 119 11 1788015805000 . . 12 119 11 1788024247000 . . 12 119 11 1788033675000 . . 12 119 11 1788042563000 . . 12 119 12 1788080534000 . . 13 119 12 1788086743000 . . 13 119 12 1788095057000 . . 13 119 12 1788104028000 . . 13 119 12 1788114686000 . . 13 119 12 1788132825000 . . 13 119 13 1788167968000 . . 14 119 13 1788168185000 . . 14 119 13 1788176805000 . . 14 119 13 1788185648000 . . 14 119 13 1788193744000 . . 14 119 13 1788204063000 . . 14 119 13 1788212818000 . . 14 end format %tcNN/DD/CCYY_HH:MM:SS obsdatetime format %tcNN/DD/CCYY_HH:MM:SS eventdatetime format %tcNN/DD/CCYY_HH:MM:SS eventdatetime1
Comment