I would like to record the responses of the adult females (n_female) in the household in sequence i.e. from R1 to R5 for Q1, from R6 to R10 for Q2 and so on. In the sample data there are two questions (Q1 and Q2). Village id and HH id are the identifiers for the given dataset.


Sample Dataset
vid hid n_female Q1_R1 Q1_R2 Q1_R3 Q1_R4 Q1_R5 Q2_R6 Q2_R7 Q2_R8 Q2_R9 Q2_R10
111 1 3 2 2 2
111 2 1 2
111 3 2 2 2
111 4 1 2
111 5 1 2 2
111 6 2 2 2 2 2
111 7 2 2 2 2 2
111 8 2 2 2 2 2
111 9 1 2 2
111 10 3 2 1 1 2 2 2
111 11 1 2 2
111 12 2 2 2 2 2

I’d like the responses to begin from R1 for each question which means that the values of the variable needs to be reassigned so that it looks like the following set.

Intended dataset
vid hid n_female Q1_R1 Q1_R2 Q1_R3 Q1_R4 Q1_R5 Q2_R6 Q2_R7 Q2_R8 Q2_R9 Q2_R10
111 1 3 2 2 2
111 2 1 2
111 3 2 2 2
111 4 1 2
111 5 1 2 2
111 6 2 2 2 2 2
111 7 2 2 2 2 2
111 8 2 2 2 2 2 2
111 9 1 2 2
111 10 3 2 1 1 2 2 2
111 11 1 2 2
111 12 2 2 2 2 2
I am unable to figure out how to accomplish this task. I tried the following code for R1 to R5 but not getting the intended output. I am using Stata 14 at the moment.

local x=2
while `x'<=5 {
foreach mem in 1 2 3 4 5 {
replace q1_`mem'=q1_`x' if (q1_`mem'==. & q1_`x'!=.)
replace q1_`x'=.
local ++x
}
}