Reshape wide to long

[Rijo John]

Dear Statalist,

I am trying to reshape the following data from a wide to a long format, but not able to because of the way variable names are entered. There is one household id variable (hhid) and multiple variables representing each member. However, each member variable is repeated 24 times (one for each member) with consecutive numbers and the subsequent variables do not start the count again from 1 to 24 but are named from 25 to 49 and so on. For example, serial number variables are entered as _v1 to _v24 for up to 24 members with a common label name "Serial Number". Then the next 24 variables are for age but entered as _v25 to _v48 (again with a common label name "age", and then the next set of 24 variables representing, say sex, entered as _v49 to _v72 (with label "sex") and so on. If they were entered as slnov1- slnov24, agev1-age24, sexv1-sex24, and so on, it would've been straightforward.

Can someone help me reshape it?

Thanks,
Rijo.

* Example generated by -dataex-. For more info, type help dataex
clear
input float hhid byte(_v1 _v2 _v3 _v49 _v50 _v51 _v73 _v74 _v75 _v97 _v98 _v99)
  1 1 2 3 1 2  3 1 2 1 32 24  7
  2 1 2 3 1 2  3 1 2 2 38 37 18
  3 1 2 3 1 2  3 1 2 1 33 30 10
  4 1 2 3 1 2  6 1 2 1 65 62 32
  5 1 2 3 1 2  3 1 2 2 62 61 26
  6 1 2 3 1 2  3 1 2 1 24 22  5
  7 1 2 3 1 3  3 2 2 2 80 45 25
  8 1 2 3 1 2  3 1 2 2 62 61 20
  9 1 2 3 1 2  3 1 2 1 53 50 12
 10 1 2 3 1 2  3 1 2 2 45 27  7
 11 1 2 3 1 2  3 1 2 1 63 62 20
 12 1 2 3 1 3  3 1 1 2 40 14 12
 13 1 2 3 1 2  3 1 2 2 45 37 16
 14 1 2 3 1 2  3 1 2 2 38 27  2
 15 1 2 3 1 2  3 1 2 2 45 40 20
 16 1 2 3 1 2  3 1 2 2 32 29  9
 17 1 2 3 1 2  3 1 2 2 57 55 26
 18 1 2 3 1 2  3 1 2 1 60 59 27
 19 1 2 3 1 2  3 1 2 2 25 27  4
 20 1 2 3 1 2  3 1 2 2 32 30  4
 21 1 2 3 1 2  3 1 2 2 18 70 31
 22 1 2 3 1 2  3 1 2 2 35 26  7
 23 1 2 3 1 2  3 1 2 2 27 19  0
 24 1 2 3 1 2  3 1 2 1 43 39 20
 25 1 2 3 1 2  3 1 2 2 25 19  1
 26 1 2 3 1 2  3 1 2 1 34 22  3
 27 1 2 3 1 2  3 1 2 1 43 37 13
 28 1 2 3 1 2  3 1 2 1 40 35 14
 29 1 2 3 1 8  3 2 1 1 40 32 24
 30 1 2 3 1 2  3 1 2 2 29 27  9
 31 1 2 3 1 2  3 1 2 2 40 39 18
 32 1 2 3 1 2  3 1 2 2 65 62 23
 33 1 2 3 1 2  3 1 2 1 30 29  4
 34 1 2 3 1 3  3 2 1 2 45 27 17
 35 1 2 3 1 2 12 1 2 1 65 60 20
 36 1 2 3 1 2  3 1 2 1 35 32 15
 37 1 2 3 1 9  8 2 2 1 63 73 40
 38 1 2 3 1 2  3 1 2 1 32 31  7
 39 1 2 3 1 2  3 1 2 1 55 60 16
 40 1 2 3 1 2  3 1 2 2 30 20  3
 41 1 2 3 1 2  3 1 2 2 30 29  2
 42 1 2 3 1 2  3 1 2 1 59 69 16
 43 1 2 3 1 2  3 1 2 1 30 29  7
 44 1 2 3 1 2  3 1 2 1 37 27  8
 45 1 2 3 1 2  3 1 2 2 70 63 14
 46 1 2 3 1 2  3 1 2 1 39 35 11
 47 1 2 3 1 2  3 1 2 1 37 35 17
 48 1 2 3 1 2  3 1 2 2 38 25  5
 49 1 2 3 1 2  3 1 2 2 60 52 25
 50 1 2 3 1 2  3 1 2 2 41 32 10
 51 1 2 3 1 2  3 1 2 2 63 49 10
 52 1 2 3 1 2  3 1 2 2 66 64 24
 53 1 2 3 1 2  3 1 2 2 38 26  8
 54 1 . . 1 .  . 1 . . 32  .  .
 55 1 2 3 1 2  3 1 2 2 38 35 15
 56 1 2 3 1 2  3 1 2 2 64 51 26
 57 1 2 3 1 3  3 1 1 2 65 39 30
 58 1 2 . 1 2  . 1 2 . 65 62  .
 59 1 2 3 1 2  3 1 2 1 40 38 16
 60 1 2 . 1 3  . 1 1 . 60 28  .
 61 1 2 3 1 2  3 1 2 1 66 42 13
 62 1 2 3 1 2  3 1 2 2 40 39 20
 63 1 2 3 1 2  3 1 2 1 44 44 14
 64 1 2 3 1 2  3 1 2 1 49 49 12
 65 1 2 3 1 2  8 1 2 2 38 35 36
 66 1 2 3 1 2  3 1 2 2 60 56 24
 67 1 2 3 1 2  3 1 2 2 38 24  2
 68 1 2 3 1 3  3 2 1 1 61 26 18
 69 1 2 3 1 2  3 1 2 1 35 42  7
 70 1 2 3 1 3  3 2 2 2 48 29 23
 71 1 . . 1 .  . 2 . . 60  .  .
 72 1 2 3 1 2  3 1 2 1 46 45 21
 73 1 2 3 1 2  3 1 2 1 64 62 23
 74 1 2 3 1 2  3 1 2 2 60 60 20
 75 1 2 3 1 2  3 1 2 1 62 60 17
 76 1 2 3 1 2  3 1 2 2 43 24  5
 77 1 2 . 1 2  . 1 2 . 64 55  .
 78 1 2 3 1 2  3 1 2 1 63 52 13
 79 1 2 3 1 2 13 1 2 1 51 48 11
 80 1 2 . 1 3  . 2 1 . 51  7  .
 81 1 2 3 1 2  3 1 2 2 71 62 20
 82 1 2 3 1 2  3 1 2 1 40 20  1
 83 1 2 3 1 2  2 1 2 2 42 39 39
 84 1 2 3 1 2  3 1 2 1 50 45 28
 85 1 2 3 1 2  3 1 2 1 41 28 12
 86 1 2 3 1 2  1 1 2 1 66 64 36
 87 1 2 3 1 2  3 1 2 2 43 43 10
 88 1 2 3 1 2  3 1 2 2 55 45 19
 89 1 2 3 1 2  3 1 2 2 36 28  9
 90 1 2 3 1 2  3 1 2 2 46 35 13
 91 1 2 3 1 3  3 2 1 1 75 37 30
 92 1 2 3 1 2  3 1 2 1 65 45 34
 93 1 2 3 1 2  3 1 2 1 28 20  1
 94 1 2 3 1 2  3 1 2 1 32 30 13
 95 1 2 3 1 2  3 1 2 1 47 46 21
 96 1 2 3 1 2  3 1 2 1 42 42 18
 97 1 . . 1 .  . 1 . . 28  .  .
 98 1 2 3 1 2  3 1 2 2 61 58 17
 99 1 2 3 1 2  3 1 2 1 43 39 19
100 1 2 . 1 3  . 1 1 . 80 24  .
end
label values _v49 labels5
label def labels5 1 "Head", modify
label values _v50 labels6
label def labels6 2 "Spouse", modify
label def labels6 3 "Daughter/Son", modify
label def labels6 8 "Sister/brother", modify
label def labels6 9 "Father/mother-in-law", modify
label values _v51 labels7
label def labels7 1 "Head", modify
label def labels7 2 "Spouse", modify
label def labels7 3 "Daughter/Son", modify
label def labels7 6 "Daughter/Son-in-law", modify
label def labels7 8 "Sister/brother", modify
label def labels7 12 "Grandparent", modify
label def labels7 13 "Other relative", modify
label values _v73 labels29
label def labels29 1 "Male", modify
label def labels29 2 "Female", modify
label values _v74 labels30
label def labels30 1 "Male", modify
label def labels30 2 "Female", modify
label values _v75 labels31
label def labels31 1 "Male", modify
label def labels31 2 "Female", modify

[Fei Wang]

Below is an example where you can rename original variables before reshaping.

clear

forvalues i = 1(1)72 {
    gen _v`i' = .
}

local varlist "slno age sex"
local i = 0
foreach var of local varlist {
    ren (_v`=1+24*`i''-_v`=24*(`i'+1)') `var'#, addnumber
    local ++i
}

[Rijo John]

Thank you so much. That did a perfect job for me. Reshape is now working just fine.

Below is an example where you can rename original variables before reshaping.

clear

forvalues i = 1(1)72 {
gen _v`i' = .
}

local varlist "slno age sex"
local i = 0
foreach var of local varlist {
ren (_v`=1+24*`i''-_v`=24*(`i'+1)') `var'#, addnumber
local ++i
}