Identifying the last time a variable had a value of 1 in panel data

[Sarah Haynes]

Hello,

I have a wide dataset that has IDs (variable name "id"), and up to 12 dates (date0, date1, date2, etc.) when someone could have experienced an event or not (event0, event1, event2, etc.). (See dataex example below). I am wanting to create two variables that indicate 1) the last date with non-missing values and 2) which event was the last one coded as "1."

So for ID=1, the first variable would be 12 (since they have non-missing data for the last event, event12) and the second variable would also be 12 (since the last month is coded as a 1).

Any help appreciated!

Sarah

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id float(date0 date1 date2 date3 date4 date5 date6 date7 date8 date9 date10 date11 date12 event0 event1 event2 event3 event4 event5 event6 event7 event8 event9 event10 event11 event12)
 1 21759 21809 21843     .     .   21910   21941 21964 21979 22091 22029 22091 22091 . 1 1 . . 1 1 1 1 1 1 1 1
 2 21763 21795 21819 21860 21879   21910   21941 21957 21997 22026 22054 22089 22096 . 1 1 1 1 1 1 1 1 1 1 1 1
 3 21796 21847 21860 21879     .       .   21957     . 22026 22054     .     .     . . 1 1 1 1 1 1 1 1 0 0 1 0
 4 21818     . 21878     .     .   21957   21997 22033 22061 22096 22127 22162 22221 . 1 0 0 0 0 0 0 0 0 0 0 0
 5 21830 21861 21913 21927     .   21964       .     .     .     .     .     .     . . 1 1 1 . 1 . . . . . . .
 6 21839 21879     . 21963 21964       .   22033 22085 22085 22096 22137 22168 22195 . 1 . 1 1 . 1 1 1 1 1 1 1
 7 21850 21927 21927     . 21964       .       .     .     .     .     .     .     . . 1 1 0 0 . . . . . . . .
 9 21867 21948     . 21958     .   22026       .     . 22106 22137 22168     . 22258 . 1 . 1 . 0 0 0 0 0 0 0 0
10 21875     .     .     .     .       .       . 22071 22106 22134     .     .     . . . . . . 0 . 1 0 0 . . .
11 21879 21912 21941 21964 22106   22106   22106     .     . 22147 22195 22259 22259 . 1 1 1 1 1 1 1 1 1 1 1 1
12 21899     .     .     .     .       .   22074 22106 22134 22169 22217 22243 22270 . . . . . . 0 0 0 0 0 0 0
13 21917 21966     .     . 22047   22091   22120 22169 22260 22259 22260 22291 22320 . 0 . . 1 0 0 0 0 0 0 0 0
14 21931     .     .     .     .       .       .     .     .     .     .     .     . . . . . . . . . . . . . .
15 21941 21965 22002 22026 22054 -635353   22117 22138 22169 22199 22230 22259 22291 . 1 1 1 1 1 1 1 1 1 1 1 1
16 21965 22002 22026 22047     .       .   22147 22178 22183 22217     .     . 22308 . 1 1 1 1 0 0 0 0 0 1 0 0
17 21969 21999 22027 22062     .       .       .     .     .     .     .     .     . . 1 1 1 1 1 1 1 0 0 0 0 0
18 21971     . 22026 22054 22106 -641874       . 22208 22239 22271     .     .     . . 1 1 1 1 1 1 1 1 1 1 1 1
19 21976     .     . 22085 22117   22138   22169 22199 22248 22278 22642 22338 22365 . . . 1 1 1 1 1 1 1 1 1 1
20 21985     . 22039 22061 22084   22117 -642573 22199 22230 22259 22289 22348 22348 . 1 1 1 0 0 0 0 0 0 0 0 0
21 22026 22061 22084 22120     .       .   22191     .     .     .     .     . 22406 . 1 1 1 1 1 1 1 1 1 1 1 1
22 22082 22117 22153 22182 22212   22243   22269 22305 22335 22363 22393 22423 22452 . 1 1 1 1 1 1 1 1 1 1 1 1
23 22102     .     .     .     .       .       .     .     .     .     .     .     . . . . . . . . . . . . . .
24 22127 25489 22259 22259     .   22389   22372 22372     .     .     .     .     . . 1 1 1 1 1 0 0 . . . . .
25 22137 22162 22192 22248     .       .   22338 22364     .     .     .     .     . . 1 1 1 1 1 1 1 1 1 1 1 1
26 22198     . 22313 22346     .       .       .     .     .     .     .     .     . . 1 1 1 1 0 0 0 0 0 0 0 0
27 22316 22347 22382 22408 22438   22467   22497 22531 22560 22592 22617 22283 22315 . 1 1 1 1 1 1 1 1 1 1 1 1
28 22316     . 22376 22406     .       .       .     .     .     .     .     .     . . 1 1 1 1 0 0 0 0 0 0 0 0
29 22329 22357 22396 22417 22452       .   22507 22539 22567 22598 22616 22648 22679 . 1 1 1 1 1 1 1 1 1 0 0 0
30 22344 22445 22445 22445 22463   22496       . 22558     .     . 22648     .     . . 1 1 1 1 1 1 1 1 1 1 1 1
31 22358     .     .     .     .       .       .     .     .     .     .     .     . . . . . . . . . . . . . .
32 22368 22399 22454 22483     .       .       .     .     .     .     .     .     . . 1 1 1 0 0 . . . . . . .
33 22379 22412 22445 22468     .       .       .     .     .     .     .     .     . . 1 1 0 0 0 0 0 0 0 0 0 0
34 22390 22443 22489 22515 22546   22575   22652 22656 22656 22679 22705     .     . . 1 1 1 1 1 1 1 1 1 1 1 1
35 22397     . 22456 22484 22489   22546       .     .     . 22648 22680     .     . . 1 1 1 1 1 1 1 1 1 1 1 1
36 22397     .     .     .     .       .       .     .     .     .     .     .     . . . . . . . . . . . . . .
37 22417 22449 22489 22516     .   22581       .     .     .     .     .     .     . . 1 1 1 1 1 1 1 1 0 1 0 0
38 22441 22489 22516 22546 22582   22607   22634 22688 22703 22732 22763 22785 22820 . 1 1 1 1 1 1 1 1 1 1 1 1
39 22501 22532 22582 22603 22627   22658   22692 22720     . 22829 22829 22865 22897 . 1 1 1 1 1 1 1 1 1 1 1 1
40 22523 22553 22583 22614 23009   22659   22692 22720 22750 22778 22806 22834 22865 . 1 1 1 1 1 1 1 1 1 1 1 1
41 22551 22582 22614 22643 22672   22701       .     .     .     .     .     .     . . 1 1 1 1 1 1 1 1 1 1 1 1
42 22552     . 22614 22688     .   22721       .     .     .     .     .     .     . . . 1 1 1 1 1 1 1 1 1 1 1
43 22561 22561 22616 22631     .   22692   22720 22750 22781 22817 22837 22868 22899 . 1 1 1 1 1 1 1 1 1 1 1 1
44 22592     . 22652     .     .       .       .     .     .     .     .     .     . . 1 1 . . . . . . . . . .
45 22680 23112     .     .     .       .       .     .     .     .     .     .     . . 1 . . . . . . . . . . .
46 22703     . 22762 22785 22816       .   22868 22897     . 22963 22980     .     . . 1 1 1 1 0 0 0 0 0 0 0 0
47 22711 22788 22788 22838     .   22971   22971 22971 22980 23103     .     .     . . 0 0 0 . 0 0 0 0 0 . . .
48 22733 22762 22785 22816 22837   22869   22900     . 22986 23014 23043 23070 23101 . 1 1 1 1 1 1 1 1 1 1 1 1
49 22761 22850 22882 22959     .       .       .     .     .     .     .     .     . . 1 1 1 1 1 1 1 1 . . . .
50 22798 22831 22862 22897     .   23037       .     .     .     . 23121     . 23298 . 1 0 0 0 0 . 0 0 0 0 . 0
51 22828 23120     .     .     .       .       .     .     .     .     .     .     . . 0 . . . . . . . . . . .
52 22922 22958 22983 23024 23043       .       . 23132 23163 23191 23218 23254 23283 . 1 1 1 1 1 1 1 1 1 1 1 1
53 22943 22977 23049 23049     .   23122   23142 23172     .     . 23320     .     . . 1 1 1 1 1 1 1 1 1 1 . .
54 22963     .     .     .     .       .       .     .     .     .     .     .     . . . . . . . . . . . . . .
55 23028 23062 23173 23222 23279   23306   23335     . 23397     .     .     .     . . 1 1 1 1 1 1 1 1 . . . .
56 23098     .     .     .     .       .       .     .     .     .     .     .     . . . . . . . . . . . . . .
end

[Clyde Schechter]

Well, it's going to be much easier to do this in long layout than wide. If there is some good reason to go back to wide layout when this is done, you can always -reshape wide- at the end. But it is very likely that the long layout will be more effective for whatever else you will be doing anyway--most Stata data management and analysis commands work best with long data. So probably you should leave it long.

reshape long date event, i(id) j(seq)
format date %td

by id (seq), sort: egen last_nm_date_in = max(cond(!missing(date), seq, .))
by id (seq): egen last_nm_event_in = max(cond(!missing(event), seq, .))
by id (seq): egen last_event_1_in = max(cond(event == 1, seq, .))

But there is some confusion in your question or data (or both). First, it is unclear what you mean by "last" here because in many of your observations, the ordering from date0 through date12 is not chronological order. For example, for id 1, date10 = 24 apr 2020, which precedes date9 = 25 jun2020--so out of chronological order. Since you want the answers to be numbers between 0 and 12, I have given you the largest numbers for which date# and event# are non-missing, and the largest number for which event == 1. But those may or may not be the numbers corresponding to the chronologically last dates for which these conditions hold. Also, I have given you separate variables for the last non-missing date and the last non-missing event because those are not always equal. In your data there are numerous instances of date being missing but event not, or vice versa.

If you want the numbers of the variables with the chronologically latest date for which the conditions hold, that code would be different. You can post back if that is the case and I'll work it up. But I'm not posting it here because I have a suspicion that the lack of correspondence between serial 0-12 order and chronological order may be errors in your data. And, of course, if that is the case you should correct those errors and then the code shown above will work give you those answers.

[Nick Cox]

For more on the main trick Clyde Schechter is using in #2 see Section 9 in https://journals.sagepub.com/doi/pdf...867X1101100210

[Nick Cox]

I agree with @Clyde Schechter's general stance.

Here is some sample technique.

gen whenlast1 = date0 if event0 == 1 

forval j = 1/12 {
    replace whenlast1 = date`j' if (whenlast1 == . | (date`j' > whenlast1 & date`j' < .)) & event`j' == 1 
}

For more see e.g. https://journals.sagepub.com/doi/pdf...867X0900900107

[Sarah Haynes]

Oh dear! Thank you Clyde for noticing this (and thank you both for your responses). These are indeed errors. Will fix and then try out these solutions.