Changing date by ID

[Galenda Nagudi]

Hello all,

My dataset comprises of women who are being followed up and are tested for pregnancy every 3 months. Even after testing positive for pregnancy they still are followed up however on the next visit the participant's pregnancy test result is recorded as negative or is missing.

I will be running an analysis of pregnancy incidence and Cox regression. Hence the final date for each participant needs to be the date they recorded a positive pregnancy test result.

[by record_id: egen final_date = max(tpregtest_date)] This was the code for creating final_date variable. it is the last recorded date for a participant. However, for those that tested positive for pregnancy it would have to be the date they were tested and not the lastest follow-up date.

My question is how do I change the final_date for participants who tested positive for pregnancy to the same date?

I tried [bysort record_id: replace final_date = tpregtest_date if preg==1] however it only changed the date when the even was recorded. Please refer to ID 17 as an example.

For more context tpregtest_result is the pregnancy test outcome and preg is a variable I generated also standing for pregnancy test outcome.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int record_id str23 redcap_event_name byte tpregtest_result float(preg tpregtest_date final_date)
 1 "m3_arm_1"        2 0 23314 23656
 1 "m6_arm_1"        2 0 23401 23656
 1 "m9_arm_1"        2 0 23481 23656
 1 "m12_arm_1"       2 0 23568 23656
 1 "m15_arm_1"       2 0 23656 23656
 1 "enrolment_arm_1" . 0     . 23656
 2 "m3_arm_1"        2 0 23316 23652
 2 "m6_arm_1"        2 0 23447 23652
 2 "m9_arm_1"        2 0 23482 23652
 2 "m12_arm_1"       2 0 23572 23652
 2 "m15_arm_1"       2 0 23652 23652
 2 "enrolment_arm_1" . 0     . 23652
13 "m3_arm_1"        2 0 23321 23660
13 "m6_arm_1"        2 0 23405 23660
13 "m9_arm_1"        2 0 23454 23660
13 "m12_arm_1"       2 0 23569 23660
13 "m15_arm_1"       2 0 23660 23660
13 "enrolment_arm_1" . 0     . 23660
14 "enrolment_arm_1" 2 0 23321 23568
14 "m3_arm_1"        2 0 23321 23568
14 "m6_arm_1"        2 0 23405 23568
14 "m9_arm_1"        2 0 23485 23568
14 "m12_arm_1"       2 0 23568 23568
15 "m3_arm_1"        2 0 23321 23657
15 "m6_arm_1"        2 0 23405 23657
15 "m9_arm_1"        2 0 23484 23657
15 "m12_arm_1"       2 0 23569 23657
15 "m15_arm_1"       2 0 23657 23657
15 "enrolment_arm_1" . 0     . 23657
16 "m3_arm_1"        2 0 23350 23657
16 "m9_arm_1"        2 0 23484 23657
16 "m12_arm_1"       2 0 23569 23657
16 "m15_arm_1"       2 0 23657 23657
16 "m6_arm_1"        . 0     . 23657
16 "enrolment_arm_1" . 0     . 23657
17 "m12_arm_1"       1 1 23625 23625
17 "m15_arm_1"       2 0 23659 23659
17 "m6_arm_1"        . 0     . 23659
17 "m3_arm_1"        . 0     . 23659
17 "enrolment_arm_1" . 0     . 23659
18 "m3_arm_1"        2 0 23322 23572
18 "m6_arm_1"        2 0 23406 23572
18 "m9_arm_1"        2 0 23483 23572
18 "m12_arm_1"       1 1 23572 23572
18 "enrolment_arm_1" . 0     . 23572
18 "m15_arm_1"       . 0     . 23572

end
format %dM_d,_CY tpregtest_date
format %td final_date
label values tpregtest_result tpregtest_result_
label def tpregtest_result_ 1 "Positive", modify
label def tpregtest_result_ 2 "Negative", modify
label values preg preg_
label def preg_ 0 "No", modify
label def preg_ 1 "Yes", modify

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input int record_id str23 redcap_event_name byte tpregtest_result float(preg tpregtest_date)
 1 "m3_arm_1"        2 0 23314
 1 "m6_arm_1"        2 0 23401
 1 "m9_arm_1"        2 0 23481
 1 "m12_arm_1"       2 0 23568
 1 "m15_arm_1"       2 0 23656
 1 "enrolment_arm_1" . 0     .
 2 "m3_arm_1"        2 0 23316
 2 "m6_arm_1"        2 0 23447
 2 "m9_arm_1"        2 0 23482
 2 "m12_arm_1"       2 0 23572
 2 "m15_arm_1"       2 0 23652
 2 "enrolment_arm_1" . 0     .
13 "m3_arm_1"        2 0 23321
13 "m6_arm_1"        2 0 23405
13 "m9_arm_1"        2 0 23454
13 "m12_arm_1"       2 0 23569
13 "m15_arm_1"       2 0 23660
13 "enrolment_arm_1" . 0     .
14 "enrolment_arm_1" 2 0 23321
14 "m3_arm_1"        2 0 23321
14 "m6_arm_1"        2 0 23405
14 "m9_arm_1"        2 0 23485
14 "m12_arm_1"       2 0 23568
15 "m3_arm_1"        2 0 23321
15 "m6_arm_1"        2 0 23405
15 "m9_arm_1"        2 0 23484
15 "m12_arm_1"       2 0 23569
15 "m15_arm_1"       2 0 23657
15 "enrolment_arm_1" . 0     .
16 "m3_arm_1"        2 0 23350
16 "m9_arm_1"        2 0 23484
16 "m12_arm_1"       2 0 23569
16 "m15_arm_1"       2 0 23657
16 "m6_arm_1"        . 0     .
16 "enrolment_arm_1" . 0     .
17 "m12_arm_1"       1 1 23625
17 "m15_arm_1"       2 0 23659
17 "m6_arm_1"        . 0     .
17 "m3_arm_1"        . 0     .
17 "enrolment_arm_1" . 0     .
18 "m3_arm_1"        2 0 23322
18 "m6_arm_1"        2 0 23406
18 "m9_arm_1"        2 0 23483
18 "m12_arm_1"       1 1 23572
18 "enrolment_arm_1" . 0     .
18 "m15_arm_1"       . 0     .
end
format %dM_d,_CY tpregtest_date
label values tpregtest_result tpregtest_result_
label def tpregtest_result_ 1 "Positive", modify
label def tpregtest_result_ 2 "Negative", modify
label values preg preg_
label def preg_ 0 "No", modify
label def preg_ 1 "Yes", modify

by record_id (tpregtest_date), sort: egen pregnancy_date ///
    = min(cond(preg, tpregtest_date, .))
by record_id (tpregtest_date): egen last_tested = max(tpregtest_date)
by record_id: gen wanted = min(pregnancy_date, last_tested)
format pregnancy_date last_tested wanted %td

[Galenda Nagudi]

Thank you so much Clyde. The code is very understandable. Much appreciated.

[Nick Cox]

See also the FAQ on last and first dates (in Stata, that is).

Stata | FAQ: Generating the last date

This paper is however closer to expanding on Clyde Schechter 's technique.

Stata Journal | Article

[Galenda Nagudi]

Thank you for sharing the resource Nick.