Generate a cumulative sum variable based on another variable

[Kim Vaarts]

Dear all, I have long format data. See example below.

I would like a variable "wanted" as shown in the example below. This is the medicine(s) that the patient took when the event of interest occured. Patients can get different medicine types A, B, C or D. The duration on each type of medicine is included. I want to keep the medicine(s) that gave the patient the event of interest. For example patient 1 got the event at time 6.2 when he was on medicine A with a duration of 6.299666, thus keep medicine A in the dataset (see variable wanted). Patient 3 got the event after he used medicine A,B and C because the total duration on medicine A+B+C was 45.299 months (19.032999+7.633+18.633) and he got the event in month 34, thus he had to be on medicine A, B and C (and not D). Patient 3 most likely switched to medicine D due to side effects.

How do I get this for all the patients in my dataset? Thanks in advance.



----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input float id byte event float dftime str1 medicine float duration str1 wanted
1 1       6.2 "A"  6.299666 "A"
1 1       6.2 ""          . "" 
1 1       6.2 ""          . "" 
1 1       6.2 ""          . "" 
1 1       6.2 ""          . "" 
2 1 3.3666666 "A" 29.866333 "A"
2 1 3.3666666 "B"  6.366333 "" 
2 1 3.3666666 "C"  86.39967 "" 
2 1 3.3666666 "D"  6.999667 "" 
3 1 34.333332 "A" 19.032999 "A"
3 1 34.333332 "B"     7.633 "B"
3 1 34.333332 "C"    18.633 "C"
3 1 34.333332 "D"  77.09967 "" 
end

------------------ copy up to and including the previous line ------------------

[Ken Chui]

Hardly elegant, but this should do:

bysort id: gen cumm_duration = sum(duration)
bysort id: gen temp01 = sum(1) if cumm_duration > dftime
gen wanted = medicine if inlist(temp01, 1, .)

[Kim Vaarts]

Thank you very much Ken Chui It works perfeclty! My gratitude!

[Kim Vaarts]

Thank you very much Ken Chui It works perfeclty! My gratitude!