reshaping data

[Rose Matthews]

Hi i have this data

* Example generated by -dataex-. For more info, type help dataex
clear
input str9 v1 float(v2 v3 v4 v5 v6 v7 v8 v9 v10 v11 v12)
"gender"            0          0          0          0         0         0         0          0          0          0          0
"Age"              40         45         50         55        60        65        70         75         80         85         90
"inc Cost"  -4096.732 -4072.8906  -4053.416  -4041.457  -4039.64 -4049.648 -4075.639 -4117.9536 -4171.0566 -4231.0615 -4283.3975
"inc QALY"  -.3069413  -.3040651 -.29440674 -.27768388 -.2502006 -.2042741 -.1462058 -.05806535  .06411792  .19958808   .3567987
"TK qualy" 15.857925  14.933599  13.889514   12.70926 11.393097  9.958816  8.413136   6.783762   5.200871   3.756846   2.604844
end

And would like to reshape to this:

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 var1 str3 var2 str8 var3
"gender" "age" "inc Cost"
"1"      "40"  "-3682.84"
"1"      "45"  "-3673.99"
end

I have tried

reshape wide var, i(gender) j(Age)

However, as I'm not sure if it works with string.
any insight would be fantastic

[Nick Cox]

That's not going to work. gender isn't even a variable in your data, and it wouldn't serve as an identiifier if it were.

Your goal makes sense, except that you want the contents of v1 to become metadata -- variable labels or even variable names -- and having everything as string is not going to help almost any analysis that might follow.

There may be a shorter way than this, but what is below does what I think is needed.

* Example generated by -dataex-. For more info, type help dataex
clear
input str9 v1 float(v2 v3 v4 v5 v6 v7 v8 v9 v10 v11 v12)
"gender"            0          0          0          0         0         0         0          0          0          0          0
"Age"              40         45         50         55        60        65        70         75         80         85         90
"inc Cost"  -4096.732 -4072.8906  -4053.416  -4041.457  -4039.64 -4049.648 -4075.639 -4117.9536 -4171.0566 -4231.0615 -4283.3975
"inc QALY"  -.3069413  -.3040651 -.29440674 -.27768388 -.2502006 -.2042741 -.1462058 -.05806535  .06411792  .19958808   .3567987
"TK qualy" 15.857925  14.933599  13.889514   12.70926 11.393097  9.958816  8.413136   6.783762   5.200871   3.756846   2.604844
end

forval j = 1/5 {
    local label`j' = v1[`j']
}

drop v1

gen id = _n

reshape long v, i(id) j(which)

reshape wide v, j(id) i(which)

gen id = which - 1

drop which

forval j = 1/5 {
    local new = strtoname("`label`j''")
    rename v`j' `new'
}

list

     +------------------------------------------------------+
     | gender   Age    inc_Cost    inc_QALY   TK_qualy   id |
     |------------------------------------------------------|
  1. |      0    40   -4096.732   -.3069413   15.85793    1 |
  2. |      0    45   -4072.891   -.3040651    14.9336    2 |
  3. |      0    50   -4053.416   -.2944067   13.88951    3 |
  4. |      0    55   -4041.457   -.2776839   12.70926    4 |
  5. |      0    60    -4039.64   -.2502006    11.3931    5 |
     |------------------------------------------------------|
  6. |      0    65   -4049.648   -.2042741   9.958816    6 |
  7. |      0    70   -4075.639   -.1462058   8.413136    7 |
  8. |      0    75   -4117.954   -.0580654   6.783762    8 |
  9. |      0    80   -4171.057    .0641179   5.200871    9 |
 10. |      0    85   -4231.062    .1995881   3.756846   10 |
     |------------------------------------------------------|
 11. |      0    90   -4283.397    .3567987   2.604844   11 |
     +------------------------------------------------------+

Make sure to use something other than 5 if needed for your full problem.

[Nick Cox]

This works too.

clear
input str9 v1 float(v2 v3 v4 v5 v6 v7 v8 v9 v10 v11 v12)
"gender"            0          0          0          0         0         0         0          0          0          0          0
"Age"              40         45         50         55        60        65        70         75         80         85         90
"inc Cost"  -4096.732 -4072.8906  -4053.416  -4041.457  -4039.64 -4049.648 -4075.639 -4117.9536 -4171.0566 -4231.0615 -4283.3975
"inc QALY"  -.3069413  -.3040651 -.29440674 -.27768388 -.2502006 -.2042741 -.1462058 -.05806535  .06411792  .19958808   .3567987
"TK qualy" 15.857925  14.933599  13.889514   12.70926 11.393097  9.958816  8.413136   6.783762   5.200871   3.756846   2.604844
end

forval j = 1/5 { 
    local label`j' = v1[`j']
}

drop v1 

xpose, clear 

forval j = 1/5 { 
    local new = strtoname("`label`j''")
    rename v`j' `new'
}

It looks as if for the final result you don't need an identifier, as this is a set of results with presumably at least one more gender category in the full example.

[Nick Cox]

Odd, yet not odd, the power of words. The title of the question and the tentative use of reshape led me to think that this indeed could be solved by use of reshape and I was fixated on the needed trickery.

Had the question been how to transpose a dataset (really a resultsset) I would have seen a better way more quickly.

I was also thinking that this is a perverse layout for data, but I now guess that it's really a table from somewhere, perhaps even MS Excel.

Nobody's fault. As I think some philosopher remarked, the mind is more like a tram than a bus.