Hi all

I have hospital admissions data, organised so each observation is an admission. There is a variable for ethnicity, but for some patients this is different between admissions. (Likely because the way people identify changes over time, and because we can record multiple ethnicities in our admissions data, and what is recorded may differ). I want to create a variable for prioritised ethnicity, using the priority allocation system used in my country.

The data looks like

id ethnicity other variables ...
1 3
1 3
2 10
3 11
3 15
3 11

I have generated a new prioritised ethnicity variable using the current ethnicity variable. I then want to replace prioritised ethnicity values with whichever value ranks higher in our allocation system.

I have tried:

generate ethnic2 = ethnic
label variable ethnic2 "Prioritised ethnic group - unified across admissions"

quietly by id: replace ethnic2 = 21 if ethnic2 == 21 // Maori is priority 1
quietly by id: replace ethnic2 = 35 if ethnic2 == 35 // Tokelauan is priority 2
quietly by id: replace ethnic2 = 36 if ethnic2 == 36 // Fijian is priority 3
....

I didn't think this was quite right and it wasn't - when I count before and after the commands the number of admissions with each ethnicity recorded doesn't change.

Any ideas how to do this?

Many thanks

Kate