Help with replacing values

[Nader Mehri]

Hi,
I would like to reshape the below dataset in two columns such that the values for vars with odd suffices (i.e., v1, v3, v5, ...) are given in a single column. And, values with even suffices, are given in a second column. For example, my ultimate data format would look like this:

"Women" "X1" "-0.002" "(0.001)†"
"Women" "X2" "-0.124" "(0.021)***"
"Women" "X3" "-0.228" "(0.017)***"

Thanks for your suggestions.
NM

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str5 Gender str16 v1 str9 v2 str10 v3 str9 v4 str10 v5 str9 v6 str10 v7 str9 v8 str10 v9 str9 v10 str8 v11 str9(v12 v13 v14 v15 v16) str10 v17 str9 v18 str10 v19
"Women" "X1"  "-0.002" "(0.001)†" ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Women" "X2"  ""       ""           "-0.124" "(0.021)***" ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Women" "X3"  ""       ""           ""       ""           "-0.228" "(0.017)***" ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Women" "X4"  ""       ""           ""       ""           ""       ""           "-0.111" "(0.023)***" ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Women" "X4"  ""       ""           ""       ""           ""       ""           ""       ""           "-0.032" "(0.014)*" ""       ""          ""       ""          ""      ""           ""       ""          
"Women" "X6"  ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         "-0.043" "(0.011)**" ""       ""          ""      ""           ""       ""          
"Women" "X7"  ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          "-0.049" "(0.014)**" ""      ""           ""       ""          
"Women" "X9"  ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          "0.011" "(0.004)**"  ""       ""          
"Women" "X10" ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           "-0.059" "(0.014)***"
"Men"   "X1"  "-0.002" "(0.001)*"   ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Men"   "X2"  ""       ""           "-0.081" "(0.012)***" ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Men"   "X3"  ""       ""           ""       ""           "-0.143" "(0.013)***" ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Men"   "X4"  ""       ""           ""       ""           ""       ""           "-0.076" "(0.017)***" ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Men"   "X4"  ""       ""           ""       ""           ""       ""           ""       ""           "-0.027" "(0.011)*" ""       ""          ""       ""          ""      ""           ""       ""          
"Men"   "X6"  ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         "-0.027" "(0.017)"   ""       ""          ""      ""           ""       ""          
"Men"   "X7"  ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          "-0.023" "(0.009)*"  ""      ""           ""       ""          
"Men"   "X9"  ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          "0.030" "(0.004)***" ""       ""          
"Men"   "X10" ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           "-0.037" "(0.010)**" 
end

------------------ copy up to and including the previous line ------------------

[Nick Cox]

This may help I am guessing that the second X4 should be X5. FWIW, I never want to read names like X3 but always something more evocative.

* Example generated by -dataex-. For more info, type help dataex
clear
input str5 Gender str16 v1 str9 v2 str10 v3 str9 v4 str10 v5 str9 v6 str10 v7 str9 v8 str10 v9 str9 v10 str8 v11 str9(v12 v13 v14 v15 v16) str10 v17 str9 v18 str10 v19
"Women" "X1"  "-0.002" "(0.001)†" ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Women" "X2"  ""       ""           "-0.124" "(0.021)***" ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Women" "X3"  ""       ""           ""       ""           "-0.228" "(0.017)***" ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Women" "X4"  ""       ""           ""       ""           ""       ""           "-0.111" "(0.023)***" ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Women" "X5"  ""       ""           ""       ""           ""       ""           ""       ""           "-0.032" "(0.014)*" ""       ""          ""       ""          ""      ""           ""       ""          
"Women" "X6"  ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         "-0.043" "(0.011)**" ""       ""          ""      ""           ""       ""          
"Women" "X7"  ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          "-0.049" "(0.014)**" ""      ""           ""       ""          
"Women" "X9"  ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          "0.011" "(0.004)**"  ""       ""          
"Women" "X10" ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           "-0.059" "(0.014)***"
"Men"   "X1"  "-0.002" "(0.001)*"   ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Men"   "X2"  ""       ""           "-0.081" "(0.012)***" ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Men"   "X3"  ""       ""           ""       ""           "-0.143" "(0.013)***" ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Men"   "X4"  ""       ""           ""       ""           ""       ""           "-0.076" "(0.017)***" ""       ""         ""       ""          ""       ""          ""      ""           ""       ""          
"Men"   "X5"  ""       ""           ""       ""           ""       ""           ""       ""           "-0.027" "(0.011)*" ""       ""          ""       ""          ""      ""           ""       ""          
"Men"   "X6"  ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         "-0.027" "(0.017)"   ""       ""          ""      ""           ""       ""          
"Men"   "X7"  ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          "-0.023" "(0.009)*"  ""      ""           ""       ""          
"Men"   "X9"  ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          "0.030" "(0.004)***" ""       ""          
"Men"   "X10" ""       ""           ""       ""           ""       ""           ""       ""           ""       ""         ""       ""          ""       ""          ""      ""           "-0.037" "(0.010)**" 
end

gen V1 = "" 
gen V2 = "" 
local n1 = 1 

forval j = 2(2)18 { 
    local k = `j' + 1 
    replace V1 = v`j' if inlist(_n, `n1',  9 + `n1') 
    replace V2 = v`k' if inlist(_n, `n1',  9 + `n1') 
    local ++n1 
} 

drop v2-v19 

list , sepby(Gender) 

     +------------------------------------+
     | Gender    v1       V1           V2 |
     |------------------------------------|
  1. |  Women    X1   -0.002     (0.001)† |
  2. |  Women    X2   -0.124   (0.021)*** |
  3. |  Women    X3   -0.228   (0.017)*** |
  4. |  Women    X4   -0.111   (0.023)*** |
  5. |  Women    X5   -0.032     (0.014)* |
  6. |  Women    X6   -0.043    (0.011)** |
  7. |  Women    X7   -0.049    (0.014)** |
  8. |  Women    X9    0.011    (0.004)** |
  9. |  Women   X10   -0.059   (0.014)*** |
     |------------------------------------|
 10. |    Men    X1   -0.002     (0.001)* |
 11. |    Men    X2   -0.081   (0.012)*** |
 12. |    Men    X3   -0.143   (0.013)*** |
 13. |    Men    X4   -0.076   (0.017)*** |
 14. |    Men    X5   -0.027     (0.011)* |
 15. |    Men    X6   -0.027      (0.017) |
 16. |    Men    X7   -0.023     (0.009)* |
 17. |    Men    X9    0.030   (0.004)*** |
 18. |    Men   X10   -0.037    (0.010)** |
     +------------------------------------+

[Nader Mehri]

This is really helpful! Thanks. I wonder to know if this solution can be expanded to cover more variables. That is, my actual data goes through v127 and I wonder if I can replicate your suggested solution to cover v20 to v37, v38 to 56, etc.

[Nick Cox]

If it is the same idea, only the numerical constants will differ.