How to create a categorical variable (morbidity index) from longitudinal survey

[Charles Ackah]

Dear all,
I am working with panel data that consists of 3 waves: 3,4 and 5. In each wave, respondents are asked whether "The child has/had any illnesses or disabilities?". The response is coded as 1. Yes; 2. No. The related variables are w3_c_hlser, w4_c_hlser, and w5_c_hlser for waves 3,4, and 5 respectively.

There is a follow-up question that asked, "What is the main serious/illness or disability?" with multiple responses/answers. A sample of the responses is provided below for wave 3.

w3_c_hl1- What is the main serious/illness or disability? 1st answer

1. Tuberculosis

2. Other respiratory problems (asthma,

3. Physically handicapped

4. Problems with sight, hearing or speech

5. Mental problem

6. HIV/AIDS

7. Diabetes

8. Heart disease

10. Epilepsy/fits

11. Other (specify)

w3_c_hl2- What is the main serious/illness or disability? 2nd answer

2. Other respiratory problems (asthma,

3. Physically handicapped

4. Problems with sight, hearing or speech

5. Mental problem

6. HIV/AIDS

9. Cancer

10. Epilepsy/fits

11. Other (specify)

w3_c_hl3- What is the main serious/illness or disability? 3rd answer

4. Problems with sight, hearing or speech

5. Mental problem

7. Diabetes

w3_c_hl4- What is the main serious/illness or disability? 4th answer

6. HIV/AIDS

9. Cancer

I want to create a categorical variable (morbidity index) that takes the value of 0=no chronic health condition; 1=one chronic health condition; 2=two chronic health conditions; 3=three chronic health conditions; and 4=four chronic health conditions. I used the following codes, but I am not about the outcome that I got. Could you kindly assist me with any other way I could achieve this?

* Binary variable for whether the child has any illness/disability
gen morbidity= w3_c_hlser
replace morbidity= w4_c_hlser if morbidity==.
replace morbidity= w5_c_hlser if morbidity==.
drop if morbidity==.
drop if morbidity<0

* Morbidity indicator per response
******************************************
gen morbid1= w3_c_hl1
replace morbid1= w4_c_hl1 if morbid1==.
replace morbid1= w5_c_hl1 if morbid1==.

gen morbid2= w3_c_hl2
replace morbid2= w4_c_hl2 if morbid2==.
replace morbid2= w5_c_hl2 if morbid2==.

gen morbid3= w3_c_hl3
replace morbid3= w4_c_hl3 if morbid3==.
replace morbid3= w5_c_hl3 if morbid3==.

gen morbid4= w3_c_hl4
replace morbid4= w4_c_hl4 if morbid4==.
replace morbid4= w5_c_hl4 if morbid4==.


*Multimorbidity indicator (on a scale of 0 to 4)

gen multimorbidity=.
replace multimorbidity=0 if morbidity==2
replace multimorbidity=1 if morbidity==1 & morbid1!=.
replace multimorbidity=2 if multimorbidity==1 & morbid2!=.
replace multimorbidity=3 if multimorbidity==2 & morbid3!=.
replace multimorbidity=4 if multimorbidity==3 & morbid4!=.

My sample data is presented below:
************************************************** ***************************

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(w3_c_hlser w4_c_hlser w5_c_hlser w3_c_hl1 w3_c_hl2 w3_c_hl3 w3_c_hl4 w4_c_hl1 w4_c_hl2 w4_c_hl3 w4_c_hl4 w5_c_hl1 w5_c_hl2 w5_c_hl3 w5_c_hl4)
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 1 . . . . . 10 . . . . . . .
. . 1 . . . .  . . . . 4 5 . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 1 . . . . . 11 . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
2 . . . . . .  . . . . . . . .
. 2 . . . . .  . . . . . . . .
. . 2 . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
. . . . . . .  . . . . . . . .
end
label values w3_c_hlser w3_yn
label def w3_yn 2 "2. No", modify
label values w4_c_hlser w4_yn
label def w4_yn 1 "1. Yes", modify
label def w4_yn 2 "2. No", modify
label values w5_c_hlser w5_yn
label def w5_yn 1 "1. Yes", modify
label def w5_yn 2 "2. No", modify
label values w3_c_hl1 w3_hl
label values w3_c_hl2 w3_hl
label values w3_c_hl3 w3_hl
label values w3_c_hl4 w3_hl
label values w4_c_hl1 w4_hl
label values w4_c_hl2 w4_hl
label values w4_c_hl3 w4_hl
label values w4_c_hl4 w4_hl
label def w4_hl 10 "10. Epilepsy/fits", modify
label def w4_hl 11 "11. Other (specify)", modify
label values w5_c_hl1 w5_hl
label values w5_c_hl2 w5_hl
label values w5_c_hl3 w5_hl
label values w5_c_hl4 w5_hl
label def w5_hl 4 "4. Problems with sight, hearing or speech", modify
label def w5_hl 5 "5. Mental problem", modify
label var w3_c_hlser "d9 - The child has/had any illnesses or disabilities?" 
label var w4_c_hlser "d9 - The child has/had any illnesses or disabilities?" 
label var w5_c_hlser "d9 - The child has/had any illnesses or disabilities?" 
label var w3_c_hl1 "d10_1 - What is the main serious illness or disability? 1st answer" 
label var w3_c_hl2 "d10_2 - What is the main serious illness or disability? 2nd answer" 
label var w3_c_hl3 "d10_3 - What is the main serious illness or disability? 3rd answer" 
label var w3_c_hl4 "d10_4 - What is the main serious illness or disability? 4th answer" 
label var w4_c_hl1 "d10_1 - What is the main serious illness or disability? 1st answer" 
label var w4_c_hl2 "d10_2 - What is the main serious illness or disability? 2nd answer" 
label var w4_c_hl3 "d10_3 - What is the main serious illness or disability? 3rd answer" 
label var w4_c_hl4 "d10_4 - What is the main serious illness or disability? 4th answer" 
label var w5_c_hl1 "d10_1 - What is the main serious illness or disability? 1st answer" 
label var w5_c_hl2 "d10_2 - What is the main serious illness or disability? 2nd answer" 
label var w5_c_hl3 "d10_3 - What is the main serious illness or disability? 3rd answer" 
label var w5_c_hl4 "d10_4 - What is the main serious illness or disability? 4th answer"

************************************************** **********************************************

[Clyde Schechter]

It appears you want to count up the total number of morbidities reported for the child across all waves. This is much more easily done in long layout:

gen `c(obs_t)' obs_no = _n
rename (w#_c_hl#) w_c_hl#[1]_#[2]
reshape long w_c_hl, i(obs_no) j(_j) string
by obs_no (_j), sort: egen morbidity_index = count(w_c_hl)
reshape wide
rename (w_c_hl#_#) w#[2]_c_hl#[1]

Note, I have restored the data to the original wide layout, and the original variable naming scheme in the last two commands. Depending on what else you will be doing with this data, however, you might be better off leaving the data in long layout by skipping the last two commands.

Also, my guess is that in the actual data set, in addition to the variables you show, you have a unique identifier for each observation. If that is correct, there is no need to create and use an obs_no variable: just use that unique identifier variable instead.