Subtracting dates of different rows using an id variable and conditional variable

[Edger Sousa]

Hello,

I am working with a dataset of healthcare system and need to determine the days until death for each patient. The variables Start and End are Stata's date variables, Id identify the patient and Death say if the patient was death or not.
I'm trying to create variable named "Gap" like the following example. If it's possible, when the patient will still alive until the end of dataset (like Id 3 in the example) calculate the gap as the subtraction of last day for dataset and "start".

Id	Death	Start	End	Gap
1	0	21236	21239	34
1	0	21250	21255	20
1	1	21260	21270	10
2	0	21240	21241	35
2	1	21270	21275	5
3	0	21250	21270	.
3	0	21310	21311	.

Thanks to you all!

[Nick Cox]

Please use dataex. https://www.statalist.org/forums/help#stata explains.

Here is one way to do it. For more detail on the use of egen here, see section 9 in https://journals.sagepub.com/doi/pdf...867X1101100210

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(id death) int(start end) byte gap
1 0 21236 21239 34
1 0 21250 21255 20
1 1 21260 21270 10
2 0 21240 21241 35
2 1 21270 21275  5
3 0 21250 21270  .
3 0 21310 21311  .
end

. egen death_date  = mean(cond(death == 1, end,.)) , by(id)
(2 missing values generated)

. gen wanted = death_date - start
(2 missing values generated)

. l, sepby(id)

     +------------------------------------------------------+
     | id   death   start     end   gap   death_~e   wanted |
     |------------------------------------------------------|
  1. |  1       0   21236   21239    34      21270       34 |
  2. |  1       0   21250   21255    20      21270       20 |
  3. |  1       1   21260   21270    10      21270       10 |
     |------------------------------------------------------|
  4. |  2       0   21240   21241    35      21275       35 |
  5. |  2       1   21270   21275     5      21275        5 |
     |------------------------------------------------------|
  6. |  3       0   21250   21270     .          .        . |
  7. |  3       0   21310   21311     .          .        . |
     +------------------------------------------------------+

.