Counting observations with no changes/different changes in follow-up data regarding medications

[Vilma Antonov]

Hi all,

I have a dataset on approx 30,000 patients who received different medical treatments following a surgery with approx 200,000 follow-up observations in total. The changes in medications are stated in the variable MEDICINE, with 5 different values:
1) patient had the same medications as stated at the last follow-up appointment since that appointment
2) patient had changes in their medications from the last follow-up appointment
3) patient received no treatment at all between last and current follow-up appointment
4) patient had the same medications as when discharged post-surgery
5) patient received medications, but not the same as stated in previous follow-up appointment or post-surgery

I want to try find out:
- how many patients had the same treatment from surgery to last follow-up appointment (aka always 4 during all follow-ups OR always 2 during follow-ups)
- how many patients have missing values at all follow-up appointments
- how many patients have alternative 2 or 5, and how many times this changes during the follow-up period (AKA, patient has 2 two times and 5 once during the whole follow-up period)

If anyone has any ideas on how to achieve one or all of these wished above, it'd be greatly appreciated!

* Example generated by -dataex-. For more info, type help dataex
clear
input str16 id_code long surgdate double medicine float followup_date
"N4012" 17032 . 17759
"N4012" 17032 1 17392
"N4012" 17032 . 18158
"N4012" 17032 2 18495
"N4012" 17032 . 18874
"N4012" 17032 . 19261
"N4052" 18868 . 19589
"N4052" 18868 3 20003
"N4052" 18868 . 20119
"N5012" 15341 2 17668
"N5012" 15341 1 18385
"N5012" 15341 1 18715
"N5012" 15341 1 19068
"N1009" 17914 . 19481
"N1009" 17914 . 19818
"N1009" 17914 5 20230
"N1009" 17914 . 20511
"N1009" 17914 . 20877
"N1009" 17914 . 21315
"N1009" 17914 . 21624
"N1009" 17914 . 21982
"N1009" 17914 1 17983
"N1011" 17540 2 17914
"N1011" 17540 . 17726
"N1011" 17540 2 18299
"N1011" 17540 1 18642
"N1011" 17540 1 19019
"N1011" 17540 1 19405
"N2031" 18652 . 19756
"N2031" 18652 . 20104
"N2031" 18652 4 20468
"N2031" 18652 . 20859
"N2031" 18652 . 21227
"N2031" 18652 2 21581
"N2031" 18652 . 21948
"N1002" 15522 1 16366
"N1002" 15522 1 16692
"N1002" 15522 1 17585
"N1002" 15522 1 17215
"N1002" 15522 1 17384
"N1005" 18422 2 20695
"N1005" 18422 1 20359
"N1005" 18422 1 19958
"N1005" 18422 1 18825
"N1005" 18422 2 19206
"N1008" 19531 . 19614
"N1008" 19531 . 19985
"N1008" 19531 . 20302
"N1008" 19531 . 20680
"N1008" 19531 . 21049
end
format %td surgdate
format %td followup_date
label values medicine med2
label def med2 1 "As prev follow-up", modify
label def med2 2 "Changes since last follow-up", modify
label def med2 3 "None given", modify
label def med2 4 "Yes as post-surgery", modify
label def med2 5 "Not as per last follow-up", modify

[Clyde Schechter]

assert followup_date > surgdate
isid id_code followup_date, sort

egen tag = tag(id_code)

by id_code: egen fours_and_twos = total(inlist(medicine, 2, 4))
by id_code: gen byte always_same = (fours_and_twos == _N)
count if tag & always_same

by id_code: egen mcount = total(missing(medicine))
by id_code: gen byte always_missing = (mcount == _N)
count if tag & always_missing

by id_code: egen two_count = total(medicine == 2)
by id_code: egen five_count = total(medicine == 5)
gen byte has_two_or_five = min(two_count + five_count, 1)
count if tag & has_two_or_five

[Vilma Antonov]

Thank you so much. This seems like it should be working, however, I'm having a bit of issue running the code below in the full dataset:

isid id_code followup_date, sort 
 * Which produces  variables id_code and followup_date do not uniquely identify the observations r(459);

[Clyde Schechter]

So, it appears something is wrong with your data. From the way you describe your data set, it should be the case that there is at most one observation for any given patient on any given date. This error message tells you that this is not actually the case: some patient(s) has(ve) at least two observations with the same date. This would invalidate the later calculations because whatever happened on that date would be double-counted.

So you need to debug your data. Start by identifying the offending observations:

duplicates tag id_code followup_date, gen(flag)
browse if flag

will show them to you. Then you have to figure out how things went wrong and what to do about it. There are several possibilities.

It may be that the duplicated observations are pure duplicates: they agree on all the other variables as well as id_code and followup_date. In that case, you only need to eliminate all but one for each id_code followup_date combination. But don't just go ahead and remove them. Review the data management that created this data set and find out why those duplicates are there. The data management must contain an error if it produced them. And where one error is found, others often lurk. So find the bug that created this problem in the first place, and fix that and any others you find along the way. Re-generate the data set and then proceed.

It may be that the duplicated observations disagree on other variables. Then you have to figure out which, if any, of them is the correct one and preserve only that one. Or it may be that all are incorrect, or all are incomplete but they can be combined in some way to produce a single correct observation. Again, rather than just fixing it in the data set as it stands, it is best to review the data management and correct what ever error(s) created this situation in the first place.

[Vilma Antonov]

Thank you, that's exactly the issue. Working on that. Really appreciate the help!