having one line of data for each id

[Tara Boyle]

Hi
I have an id variable (form)
I have a state variable (timefromop) - which can be either preop-postop 6 months - postop 30days

I have different EQ5D scores measured at 30 days, 6 months, 12 months

I would like to move the scores into one line for each id which would appear like this:

FORM - EQ5D PREOP -EQ5D 30DAYS - EQ5D 6 MONTHS - EQ5D 12 MONTHS

Would you advise me to use rangestate or perhaps reshape?

* Example generated by -dataex-. For more info, type help dataex
clear
input int form float timefromop byte sex float(EQindex_preop EQindex_postop30d EQindex_postop6m EQindex_postio12m age_year)
 1 1 0 .704    .    . .   70.35616
 1 3 0    .    .    . .   70.35616
 2 1 0 .758    .    . .   59.09041
 3 1 1 .808    .    . .   81.45753
 3 3 1    .    .    . .   81.45753
 4 1 0 .829    .    . .   47.69589
 5 1 1 .892    .    . .    42.1863
 6 1 0 .801    .    . .  64.775345
 6 3 0    .    . .879 .  64.775345
 7 1 0 .879    .    . .   74.44109
 8 1 0 .758    .    . .   54.39452
 9 1 0 .808    .    . .    71.8274
10 1 1 .808    .    . .   67.15069
11 1 0 .833    .    . .   70.83014
12 1 0 .751    .    . . -.23835616
12 3 0    .    .    . . -.23835616
13 1 1 .808    .    . .   53.49863
13 2 1    . .942    . .   53.49863
14 1 0  .68    .    . .   58.28493
16 1 1 .808    .    . .   78.92055
16 2 1    .    1    . .   78.92055
17 1 0 .808    .    . .  66.353424
17 2 0    . .937    . .  66.353424
17 3 0    .    .    . .  66.353424
18 1 0 .758    .    . .   60.09041
18 2 0    . .879    . .   60.09041
19 1 1 .829    .    . .   57.84109
20 1 0 .758    .    . .  66.224655
21 1 0 .779    .    . .   71.37808
22 1 0  .73    .    . .          .
end
label values timefromop r10
label def r10 1 "preop", modify
label def r10 2 "postop30days", modify
label def r10 3 "postop6M", modify

[Nick Cox]

Does this help? I can't see that rangestat (SSC) is relevant here.

* Example generated by -dataex-. For more info, type help dataex
clear
input int form float timefromop byte sex float(EQindex_preop EQindex_postop30d EQindex_postop6m EQindex_postio12m age_year)
 1 1 0 .704    .    . .   70.35616
 1 3 0    .    .    . .   70.35616
 2 1 0 .758    .    . .   59.09041
 3 1 1 .808    .    . .   81.45753
 3 3 1    .    .    . .   81.45753
 4 1 0 .829    .    . .   47.69589
 5 1 1 .892    .    . .    42.1863
 6 1 0 .801    .    . .  64.775345
 6 3 0    .    . .879 .  64.775345
 7 1 0 .879    .    . .   74.44109
 8 1 0 .758    .    . .   54.39452
 9 1 0 .808    .    . .    71.8274
10 1 1 .808    .    . .   67.15069
11 1 0 .833    .    . .   70.83014
12 1 0 .751    .    . . -.23835616
12 3 0    .    .    . . -.23835616
13 1 1 .808    .    . .   53.49863
13 2 1    . .942    . .   53.49863
14 1 0  .68    .    . .   58.28493
16 1 1 .808    .    . .   78.92055
16 2 1    .    1    . .   78.92055
17 1 0 .808    .    . .  66.353424
17 2 0    . .937    . .  66.353424
17 3 0    .    .    . .  66.353424
18 1 0 .758    .    . .   60.09041
18 2 0    . .879    . .   60.09041
19 1 1 .829    .    . .   57.84109
20 1 0 .758    .    . .  66.224655
21 1 0 .779    .    . .   71.37808
22 1 0  .73    .    . .          .
end
label values timefromop r10
label def r10 1 "preop", modify
label def r10 2 "postop30days", modify
label def r10 3 "postop6M", modify
label def r10 4 "postio12m", modify 

egen EQindex = rowmin(EQindex_*) 
drop EQindex_* 

reshape wide EQindex, i(form) j(timefromop) 

list 

     +---------------------------------------------------------+
     | form   EQindex1   EQindex2   EQindex3   sex    age_year |
     |---------------------------------------------------------|
  1. |    1       .704          .          .     0    70.35616 |
  2. |    2       .758          .          .     0    59.09041 |
  3. |    3       .808          .          .     1    81.45753 |
  4. |    4       .829          .          .     0    47.69589 |
  5. |    5       .892          .          .     1     42.1863 |
     |---------------------------------------------------------|
  6. |    6       .801          .       .879     0    64.77534 |
  7. |    7       .879          .          .     0    74.44109 |
  8. |    8       .758          .          .     0    54.39452 |
  9. |    9       .808          .          .     0     71.8274 |
 10. |   10       .808          .          .     1    67.15069 |
     |---------------------------------------------------------|
 11. |   11       .833          .          .     0    70.83014 |
 12. |   12       .751          .          .     0   -.2383562 |
 13. |   13       .808       .942          .     1    53.49863 |
 14. |   14        .68          .          .     0    58.28493 |
 15. |   16       .808          1          .     1    78.92055 |
     |---------------------------------------------------------|
 16. |   17       .808       .937          .     0    66.35342 |
 17. |   18       .758       .879          .     0    60.09041 |
 18. |   19       .829          .          .     1    57.84109 |
 19. |   20       .758          .          .     0    66.22466 |
 20. |   21       .779          .          .     0    71.37808 |
     |---------------------------------------------------------|
 21. |   22        .73          .          .     0           . |
     +---------------------------------------------------------+

[Tara Boyle]

many thanks, was playing around with reshape and couldn't get it to work, thanks for enlightening me !

[Erik Ruzek]

I would suggest creating a single EQ5D variable that takes on the appropriate value at a given timepoint. Once that is done, you can remove the various EQ variables and reshape.

gen EQ5D = EQindex_preop
drop EQindex_preop
foreach v of varlist EQindex_postop30d EQindex_postop6m EQindex_postio12m {
    replace EQ5D = `v' if missing(EQ5D)
    drop `v'
}

reshape wide EQ5D age_year, i(form) j(timefromop)