reshape data from wide to long format with two j variables.

[Inaamul Haq]

I have a data in wide format. Each individual patient is identified using the

ptid

variable. The data is given below.

* Example generated by -dataex-. For more info, type help dataex
clear
input int ptid byte(ket_reason madrs0_1 madrs2_1 madrs24_1 madrs0_2 madrs2_2 madrs24_2 madrs0_3 madrs2_3 madrs24_3 madrs0_4 madrs2_4 madrs24_4 madrs0_5 madrs2_5 madrs24_5 madrs0_6 madrs2_6 madrs24_6)
  1 2 37 23 17 24 22 22 24 15 15 21 21 17 29 21 21 24 24 24
  2 2 29 24 24 24 21 20 23 23 23 20 20 20 18 17 11 17 16 11
  3 0 39 29 19 20 20 18 13 12 13 22 22 15 22 19 18 18 17 12
  4 0 29 22 20 17 15 15 16 16 14 14 14 12 10 10  9  9  9  8
  5 0 20 17 15 14 12 10 13 13 12 14 14 13 12 12 11 11 10  6
  6 2 32 32 32 32 32 32 32 32 32  .  .  .  .  .  .  .  .  .
  7 0 20 20 17 18 18 18 15 14 10 12 11  9 10 10  6  8  7  6
  8 0 24 23 21 22 22 14 19 17 17 20 20 19 16 15 11 12 11 11
  9 0 29 26 14 16 15 10  9  8  7  7  7  5  .  .  .  .  .  .
 10 0 17 17 14 16 16 18 18 15 12 10 10 11  8  7  7  6  5  6
 11 2 30 30 30 30 30 30  .  .  .  .  .  .  .  .  .  .  .  .
 12 2 30 30 30 30 30 30  .  .  .  .  .  .  .  .  .  .  .  .
 13 1 16 14 14 15 13  9  8  7  6  .  .  .  .  .  .  .  .  .
 14 2 40 35 35  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .
 15 0 20 18 18 17 15 17 14 13 13 20 18 19 14 13 13 10 10 10
 16 2 34 32 32 33 33 29 31 30 29 30 29 28 24 24 22 26 25 25
 17 2 27 23 20 25 18 14 18 17 12 12 11 10  8  7  7  6  6  6
 18 2 43 36 36 28 25 25 20 19 19 17 17 15 14 13 13 16 15 13
 19 2 26 26 24 21 20 20 20 19 16 15 14 12 12 10  9  8  7 24
 20 0 22 19 18 20 18 16 17 16 14 11  9  9  8  8  7  8  8  7
 21 2 27 21 18 18 16 16 16 16 15 13 13 13 15 15 15 15 15 15
 22 0 19 18 17 17 16 16 16 15 15 15 15 15 14 14 14 14 14 14
 23 2 29 29 29 29 29 29  .  .  .  .  .  .  .  .  .  .  .  .
 24 2 31 29 26 18 18 17 15 14 14 12 12 12 11 11 11 11 11 11
 25 0 20 18 18 18 17 16 18 17 17 15 15 15 14 14 14 14 14 14
 26 2 22 12 11 14 13  8  8  6  4  6  4  3  3  3  3  2  2  2
 27 0 28 28 22 26 21 20 18 16 15 12 12 11 10  9  9  9  9  7
 28 0 27 24 21 20 18 16 15 12 12 10 10  9  7  6  6  6  5  5
 29 2 34 31 29 30 28 24 22 21 17 15 15 13 13 11 10 10  9  9
 30 2 27 24 22 21 18 18 15 14 13 12 11 10  9  8  7  7  6  5
 31 0 25 24 21 22 20 19 15 14 14 12 12 11 10  9  9  8  7  7
 32 2 28 28 28 28 28 30  .  .  .  .  .  .  .  .  .  .  .  .
 33 2 27 20 20 23 21 21 18 17 16 15 14 13 13 10 10 10  9  8
 34 0 21 21 19 20 19 18 19 17 16 14 12 12 10 10  9  7  6  6
 35 1 15 13 12 13 10  8  8  6  5  .  .  .  .  .  .  .  .  .
 36 0 25 23 21 19 17 17 14 13 12 11 10  9  8  7  6  6  5  5
 37 0 22 19 18 20 18 16 17 16 14 11 11  9  8  8  7  8  8  7
 38 1 24 23 23 23 22 22 23 22 22 21 21 20 21 20 20 20 19 19
 39 0 28 27 26 24 23 22 21 18 19 18 16 15 16 15 14 16 14 14
 40 0 24 23 23 23 22 22 23 22 22 21 21 20 21 20 20 20 19 19
 41 2 26 24 24 24 23 23 23 19 18 19 17 16 16 14 13 10  9  9
 42 0 25 25 24 24 24 22 23 22 20 20 20 19 22 20 20 20 19 19
 43 0 25 24 23 22 22 21 21 19 18 18 17 15 16 14 13 14 12 11
 44 2 27 23 20 25 18 14 18 17 12 12 11 10  8  7  7  6  6  6
 45 0 19 18 17 17 16 16 16 15 15 15 15 15 14 14 14 14 14 14
 46 0 30 28 26  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .
 47 0 34 26 26 24 21 20 20 20 19 16 15 14 12 12 10  9  8  7
 48 0 34 31 29 30 28 24 22 21 17 15 15 13 13 11 10 10  9  9
 49 0 28 27 26 24 23 22 21 18 19 18 16 15 16 15 14 16 14 14
 50 2 24 23 23 23 22 22 23 22 22 21 21 20 21 20 20 20 19 19
 51 0 43 36 36 28 25 25 20 19 19 17 17 15 14 13 13 16 15 13
 52 0 43 36 36 28 25 25 20 19 19 17 17 15 14 13 13 16 15 13
 53 1 24 23 23 23 22 22 23 22 22  .  .  .  .  .  .  .  .  .
 54 1 24 23 23 23 22 22 23 22 22 21 21 20 21 20 20 20 19 19
 55 0 20 18 18 17 15 17 14 13 13 20 18 19 14 13 13 10 10 10
 56 2 27 21 18 18 16 16 16 16 15 13 13 13 15 15 15 15 15 15
 57 0 27 23 20 25 18 14 18 17 12 12 11 10  8  7  7  6  6  6
 58 0 28 27 26 24 23 22 21 18 19 18 16 15 16 15 14 16 14 14
 59 1 20 18 18 18 17 16 18 17 17 15 12 12  8  6  6  5  5  5
 60 2 30 29 29 30 30 30  .  .  .  .  .  .  .  .  .  .  .  .
 61 2 31 29 26 18 18 17 15 14 14 12 12 12 11 11 11 11 11 11
 62 2 34 31 29 30 28 24 22 21 17 15 15 13 13 11 10 10  9  9
 63 2 29 24 24 24 21 20 23 23 23 20 20 20 18 17 11 17 16 11
 64 1 18 17 16 14 13 12 11  8  9  8  6  5  6  5  4  6  4  4
 65 2 27 20 20 23 21 21 18 17 16 15 14 13 13 10 10 10  9  8
 66 0 29 26 23 24 22 19 18 17 14 15 14 11 12 10  8  9  7  5
 67 0 29 26 14 16 15 10  9  8  7  7  7  5  .  .  .  .  .  .
 68 0 17 17 14 16 16 18 18 15 12 10 10 11  8  7  7  6  5  6
 69 2 34 34 34 34 34 30  .  .  .  .  .  .  .  .  .  .  .  .
 70 1 19 18 16 17 16 14 15 13 12 12 11  9 10  9  7  7  5  4
 71 1 15 12 12  8  6  6  5  5  5  .  .  .  .  .  .  .  .  .
 72 2 39 39 37  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .
 73 0 20 18 18 17 15 17 14 13 13 20 18 19 14 13 13 10 10 10
 74 2 34 32 32 33 33 29 31 30 29 30 29 28 24 24 22 26 25 25
 75 2 27 23 20 25 18 14 18 17 12 12 11 10  8  7  7  6  6  6
 76 2 43 36 36 28 25 25 20 19 19 17 17 15 14 13 13 16 15 13
 77 2 26 26 24 21 20 20 20 19 16 15 14 12 12 10  9  8  7 24
 78 0 22 19 18 20 18 16 17 16 14 11  9  9  8  8  7  8  8  7
 79 2 27 21 18 18 16 16 16 16 15 13 13 13 15 15 15 15 15 15
 80 0 19 18 17 17 16 16 16 15 15 15 15 15 14 14 14 14 14 14
 81 2 32 32 32 32 32 32  .  .  .  .  .  .  .  .  .  .  .  .
 82 2 31 29 26 18 18 17 15 14 14 12 12 12 11 11 11 11 11 11
 83 0 20 18 18 18 17 16 18 17 17 15 15 15 14 14 14 14 14 14
 84 0 25 24 23 22 22 21 21 19 18 18 17 15 16 14 13 14 12 11
 85 2 27 23 20 25 18 14 18 17 12 12 11 10  8  7  7  6  6  6
 86 0 26 24 19 20 18 16 17 14 13 14 13 10 11 10  8  9  7  5
 87 0 17 17 14 16 16 18 18 15 12 10 10 11  8  7  7  6  5  6
 88 2 22 12 11 14 13  8  8  6  4  6  4  3  3  3  3  2  2  2
 89 0 30 28 22 26 21 20 18 16 15 12 12 11 10  9  9  9  9  7
 90 0 27 24 21 20 18 16 15 12 12 10 10  9  7  6  5  4  3  3
 91 2 33 31 28 30 28 24 26 24 20 22 18 15 20 18 15 17 15 13
 92 2 27 24 22 21 18 18 15 14 13 12 11 10  9  8  7  7  6  5
 93 0 26 24 20 22 20 17 19 17 14 14 12  9 11 10  7  9  7  6
 94 0 27 23 20 25 18 14 18 17 12 12 11 10  8  7  7  6  6  6
 95 0 28 27 26 24 23 22 21 18 19 18 16 15 16 15 14 16 14 14
 96 1 20 18 18 18 17 16 18 17 17 15 12 12  8  6  6  5  5  5
 97 2 32 31 31 32 32 32  .  .  .  .  .  .  .  .  .  .  .  .
 98 2 30 29 26 24 20 19 22 19 17 19 16 13 15 14 12 13 13 12
 99 0 43 36 36 28 25 25 20 19 19 17 17 15 14 13 13 16 15 13
100 1 24 23 20 22 21 18 20 20 17 19 17 15 17 15 12 15 13 10
end
label values ket_reason labels8
label def labels8 0 "Resistance to oral medication", modify
label def labels8 1 "Active suicidality", modify
label def labels8 2 "Both", modify
label values madrs0_2 labels12
label values madrs2_2 labels12
label values madrs24_2 labels13
label values madrs0_3 labels14
label values madrs2_3 labels15
label values madrs24_3 labels16
label values madrs0_4 labels17
label values madrs2_4 labels18
label values madrs24_4 labels19
label values madrs0_5 labels20
label values madrs2_5 labels21
label values madrs24_5 labels22
label values madrs0_6 labels23
label values madrs2_6 labels24
label values madrs24_6 labels25

I want to convert the data into a long format. The madrs is measured at three time points (0, 2, and 24) across 6 cycles (1, 2, 3, 4, 5, 6). So madrs0_1 is madrs measured at time 0 in cycle 1 and madrs24_6 is madrs measured at time 24 in cycle 6. I want to convert this data into long form with the following variables: ptid, ket_reason, cycle, time, and madrs. How can I do that?

[Mike Lacy]

I believe this does what you want:

reshape long madrs, i(ptid) j(timecycle) string
// Separate and create numeric variables out of the "timecycle" string
split timecycle, gen(tc) parse("_")
destring tc1 tc2, gen(time cycle)
drop timecycle tc1 tc2

[Inaamul Haq]

Thank you very much. The code was perfect.

[Clyde Schechter]

One aside to Mike Lacy 's excellent advice.

The -split- command has a -destring- option. So you could replace the -split-, -destring-, and -drop- commands shown in #2 with:

split timecycle, gen(tc) parse("_") destring
rename (tc1 tc2) (time cycle)
drop timecycle

I would characterize the difference between this and #2 is purely stylistic, a matter of taste. I see no performance or code-readability reason to prefer either over the other.

[Mike Lacy]

Thanks Clyde, I never noticed that option before.