Reassign variable values

[Falguni Patel]

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
}
}

[William Lisowski]

Welcome to Statalist.

I think this revised version of your code does what you want.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int vid byte(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 .
end

generate move = 0

forvalues qq = 1/2 {
    local mf = `qq' * 5 - 4
    local ml = `qq' * 5 - 1
    forvalues mm = `mf'/`ml' {
        local xf = `mm' + 1
        local xl = `ml' + 1
        forvalues xx = `xf'/`xl' {
            quietly replace move = q`qq'_r`mm'==. & q`qq'_r`xx'!=.
            quietly replace q`qq'_r`mm' = q`qq'_r`xx' if move  
            quietly replace q`qq'_r`xx' = .           if move
        }
    }
}
drop move
list, clean noobs

. list, clean noobs

    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       .       .        .

[Falguni Patel]

Thanks William for welcoming me and indeed for the revised code. It made my task easier. Appreciate your help.