Creating a new observation per unique patient ID

[Susan Gutierrez]

Hi all,

I want to create a new observation row for each unique patient ID in my data set. I would like it to display as the last observation per patient ID.

I have considered using "expand" to duplicate a prior observation as I will be keeping the content of most variables the same. However, there are a few variables that I will need to edit after creating the observation ("start" "end" and "time"), and I imagine it would be easiest if I start by creating the new observations with blank entries for these variables ("start" "end" "time").

Here is my current set up:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(patient_id start end time status)
1    0  361  361 1
1  361  375  375 1
1  375  452  452 1
1  452  491  491 1
1  491  515  515 1
1  515  606  606 1
1  606  635  635 1
1  635  680  680 1
2    0  313  313 1
2  313  345  345 1
2  345  732  732 1
4    0  888  888 1
4  888  910  910 1
4  910  932  932 1
4  932  980  980 1
4  980 1024 1024 1
4 1024 1073 1073 1
4 1073 1218 1218 1
4 1218 1292 1292 1
end

This is what I would like the data to look like (see final row per patient ID):

* Example generated by -dataex-. For more info, type help dataex
clear
input float(patient_id start end time status)
1    0  361  361 1
1  361  375  375 1
1  375  452  452 1
1  452  491  491 1
1  491  515  515 1
1  515  606  606 1
1  606  635  635 1
1  635  680  680 1
1 . . . 1
2    0  313  313 1
2  313  345  345 1
2  345  732  732 1
2 . . . 1
4    0  888  888 1
4  888  910  910 1
4  910  932  932 1
4  932  980  980 1
4  980 1024 1024 1
4 1024 1073 1073 1
4 1073 1218 1218 1
4 1218 1292 1292 1
4 . . . 1
end

I appreciate any suggestions on how to create a new observation per patient ID -- thank you!

[Clyde Schechter]

I don't see any better way than using -expand- and then clobbering the values in the added observation with missing values:

by patient_id (start), sort: gen expander = 2*(_n == _N)
expand expander
drop expander
foreach v of varlist time end start {
    by patient_id (start), sort: replace `v' = . if _n == _N
}

[Susan Gutierrez]

Yes - that worked! Thanks Clyde.