Help with data set-up

[Galina Hayes]

Hello all- I feel like there is an easy solution to this but I just can't see how to do it. I have data representing hours of observation in a medical ICU, across 4 hospitals. One observation=one hour. Within that hour, there will be staff coverage from between 1 and up to 8 nurses. These are listed as variables nurse_ID_1, nurse_ID_2, etc. The are between 20 and 28 different nurses providing coverage over the study period, depending on the hospital. I have a separate data set which contains information on total years of experience for each nurse, by nurse ID. I would like to generate a variable in my main data set which represents total number of years of experience available from the nursing staff for that shift hour. Any thoughts on how?

[Mike Lacy]

We don't know what your data structure is, so I've made a guess. (See the instructions in the FAQ re -dataex- for posting example data. It would help you in the future.) I'm assuming you want total experience within a particular hospital, although you didn't say.

// simulate data
clear
set obs 3  // 3 hours
gen byte hour = _n
expand 5  // 5 hospitals
sort hour
gen byte hospital = _n
forval i = 1/8 {  // up to 8 nurses
   gen int nurse_id`i' = ceil(runiform() * 10) //  10 nurses to choose from
}
list // Hope this looks like your data.
// end data
//
//  Long format makes this easier.
reshape long nurse_id, i(hour hospital) j(j)
drop j // was just there to satisfy reshape
//
merge m:1 nurse_id using YourExperienceFile.dta  // I didn't feel like simulating this.
// Assume a variable "experience" was obtained in the merge
bysort hospital hour : egen TotalExperience = total(experience)

[Galina Hayes]

Thanks Mike very helpful.