Mark Variable if Before or After Specific Date by Group

[Hans Schubert]

Good morning,

Would appreciate some help with this please.

Looking to code the 'wanted' column

The group variable is 'pet'
If 'petcode' = 2 I need to put a '3' in the wanted column for all ddates that occurred before 'petcode' = 1 for that pet
If 'petcode' = 2 I need to put a '4' in the wanted column for all ddates that occurred after 'petcode' = 1 for that pet
If 'petcode' = 1 Just put a '1' in the wanted column

Thanks

* Example generated by -dataex-. For more info, type help dataex
clear
input float ddate str16 pet byte petcode float wanted
22397 "Dog" 2 3
22611 "Dog" 1 1
22653 "Dog" 2 4
22686 "Dog" 2 4
22722 "Dog" 2 4
22765 "Dog" 2 4
22854 "Dog" 2 4
22907 "Dog" 2 4
22930 "Dog" 2 4
22983 "Dog" 2 4
23005 "Dog" 2 4
23046 "Dog" 2 4
23084 "Dog" 2 4
23109 "Dog" 2 4
23128 "Dog" 2 4
22876 "Cat" 1 1
22897 "Cat" 2 4
22906 "Cat" 2 4
22983 "Cat" 2 4
23005 "Cat" 2 4
23225 "Cat" 2 4
end
format %td ddate

[Nick Cox]

* Example generated by -dataex-. For more info, type help dataex
clear
input float ddate str16 pet byte petcode float wanted
22397 "Dog" 2 3
22611 "Dog" 1 1
22653 "Dog" 2 4
22686 "Dog" 2 4
22722 "Dog" 2 4
22765 "Dog" 2 4
22854 "Dog" 2 4
22907 "Dog" 2 4
22930 "Dog" 2 4
22983 "Dog" 2 4
23005 "Dog" 2 4
23046 "Dog" 2 4
23084 "Dog" 2 4
23109 "Dog" 2 4
23128 "Dog" 2 4
22876 "Cat" 1 1
22897 "Cat" 2 4
22906 "Cat" 2 4
22983 "Cat" 2 4
23005 "Cat" 2 4
23225 "Cat" 2 4
end
format %td ddate

gen WANTED = 1 if petcode == 1
egen first1 = min(cond(petcode == 1, ddate, .)), by(pet)
replace WANTED = 3 if petcode == 2 & ddate < first1
replace WANTED = 4 if petcode == 2 & ddate > first1  

assert wanted == WANTED

list, sepby(pet)

See also Section 9 of https://journals.sagepub.com/doi/pdf...867X1101100210

The code will return a missing date if petcode 1 is never observed for any pet.

[Hans Schubert]

Thanks, Nick. That worked fine.