Reshaping doubly wide data to long

[Victor Avila-Quintero]

Hi everyone,

I am working in a network meta-analysis and have data for each intervention (1 to 6) and for each rating scale used in those studies (1 to 3) for which these data were collected at multiple timepoints (baseline, at 30 min, 60 min, 120 min and 180 min). I tried to use this article from UCLA, but sample size and name of the rating scales data are not being properly inputed repeatedly for the new created observations after reshaping the dataset. Here is an example of the long dataset after reshaping that I am getting after following the steps on the UCLA article. Variables "intervention" and "RS" are the number of the intervention and the number of the rating scale respectively.

* Example generated by -dataex-. For more info, type help dataex
clear
input float ID str27 treatment int n double(bm bsd ym ysd) str152 RatingScale byte(intervention RS)
11 "Haloperidol + Promethazine"  27  1.4  .6    .    . "ACES"                                                                      1 1
11 "Haloperidol + Midazolam"     25  1.8  .7    .    . "PANSS-EC"                                                                  2 1
11 "Ziprasidone"                 23  1.7  .7    .    . ""                                                                          3 1
11 "Olanzapine"                  25  1.7  .6    .    . ""                                                                          4 1
11 ""                             .    .   .    .    . ""                                                                          5 1
11 ""                             .    .   .    .    . ""                                                                          6 1
11 ""                             . 25.7   6 10.7  6.7 ""                                                                          1 2
11 ""                             . 24.3 5.6  9.4  9.4 ""                                                                          2 2
11 ""                             . 26.4 6.4 11.2  8.3 ""                                                                          3 2
11 ""                             . 24.4 5.9  9.2  5.3 ""                                                                          4 2
11 ""                             .    .   .    .    . ""                                                                          5 2
11 ""                             .    .   .    .    . ""                                                                          6 2
11 ""                             .    .   .    .    . ""                                                                          1 3
11 ""                             .    .   .    .    . ""                                                                          2 3
11 ""                             .    .   .    .    . ""                                                                          3 3
11 ""                             .    .   .    .    . ""                                                                          4 3
11 ""                             .    .   .    .    . ""                                                                          5 3
11 ""                             .    .   .    .    . ""                                                                          6 3
19 "Clonazepam + Placebo"         8  5.5 1.2  2.2  1.3 "Other: CGI Severity Scale"                                                 1 1
19 "Haloperidol + Procyclidine"   8  5.4 1.1  2.6  1.3 "Other: Target Manic Symptoms"                                              2 1
19 ""                             .    .   .    .    . "Other: Inpatient Multidimensional Psychiatric Scale (IMPS) Manic Subscale" 3 1
19 ""                             .    .   .    .    . ""                                                                          4 1
19 ""                             .    .   .    .    . ""                                                                          5 1
19 ""                             .    .   .    .    . ""                                                                          6 1
19 ""                             .   17 3.3  7.3  4.9 ""                                                                          1 2
19 ""                             .   18 3.2    5  4.9 ""                                                                          2 2
19 ""                             .    .   .    .    . ""                                                                          3 2
19 ""                             .    .   .    .    . ""                                                                          4 2
19 ""                             .    .   .    .    . ""                                                                          5 2
19 ""                             .    .   .    .    . ""                                                                          6 2
19 ""                             . 16.8   3   10  7.2 ""                                                                          1 3
19 ""                             . 16.6 5.2  7.4  3.8 ""                                                                          2 3
19 ""                             .    .   .    .    . ""                                                                          3 3
19 ""                             .    .   .    .    . ""                                                                          4 3
19 ""                             .    .   .    .    . ""                                                                          5 3
19 ""                             .    .   .    .    . ""                                                                          6 3
30 "Staccato Placebo"           105 17.7   . 12.9    . "PANSS-EC"                                                                  1 1
30 "Loxapine"                   104 17.4   . 9.31    . "ACES"                                                                      2 1
30 "Loxapine"                   105 17.3   . 8.27    . "Other: "                                                                   3 1
30 ""                             .    .   .    .    . ""                                                                          4 1
30 ""                             .    .   .    .    . ""                                                                          5 1
30 ""                             .    .   .    .    . ""                                                                          6 1
30 ""                             . 2.01  .4 3.34 1.68 ""                                                                          1 2
30 ""                             . 2.11 .44 4.67 1.98 ""                                                                          2 2
30 ""                             . 2.11 .42 5.14 2.06 ""                                                                          3 2
30 ""                             .    .   .    .    . ""                                                                          4 2
30 ""                             .    .   .    .    . ""                                                                          5 2
30 ""                             .    .   .    .    . ""                                                                          6 2
30 ""                             .    .   .    .    . ""                                                                          1 3
30 ""                             .    .   .    .    . ""                                                                          2 3
30 ""                             .    .   .    .    . ""                                                                          3 3
30 ""                             .    .   .    .    . ""                                                                          4 3
30 ""                             .    .   .    .    . ""                                                                          5 3
30 ""                             .    .   .    .    . ""                                                                          6 3
end

So, treatment should have been inputed twice for study 11 and 30 and three times for study 19, same as sample size. And the rating scale does not even get inputed repeatedly for the first 6 interventions. That is the issue that I am having.

Here is a sample set and the code line I am using:

* Example generated by -dataex-. For more info, type help dataex
clear
input float ID str26 treatment1 str27 treatment2 int(n1 n2) double(bm1 bm2 bsd1 bsd2 ym1 ym2 ysd1 ysd2) str83 RatingScale1 str152 RatingScale2
 1 "Haloperidol"                "Haloperidol"              21  12    1    1   .    .     .  . . . "Other: Five-point combativeness scale"     ""            
 2 "Midazolam"                  "Haloperidol"              42  42    .    .   .    .     .  . . . ""                                          ""            
 3 "Droperidol"                 "Ziprasidone"              25  28    3    3   .    .    -1 -3 . . "Other: Altered Mental Status Scale (AMSS)" "Other: "     
 4 "Droperidol"                 "Ziprasidone"              50  46 3.12 3.41   .    . -1.68 -2 . . "Other: Altered Mental Status Scale (AMS)"  ""            
 5 "Ketamine"                   "Haloperidol"              45  45 2.62 2.59 .94 1.13     .  . . . "Other: altered mental status score (AMSS)" ""            
 6 "Haloperidol"                "Midazolam"                23  25    .    .   .    .     .  . . . "Other: Restless score"                     ""            
 7 "Olanzapine"                 "Haloperidol"              54  57 3.96 4.15   .    .     .  . . . "Other: Agitation/Sedation Scale"           ""            
 8 "Ketamine"                   "Haloperidol + Midazolam"  40  40 3.64 3.45   .    .     .  . . . "RASS"                                      ""            
 9 "Lorazepam"                  "Droperidol"              100 102  5.3  5.6  .7   .6     .  . . . "Other: Sedation Scale"                     ""            
10 "Haloperidol + Promethazine" "Olanzapine"              150 150 4.63 4.55 .72  .78     .  . . . "Other: CGI-S"                              "Other: CGI-I"
11 "Haloperidol + Promethazine" "Haloperidol + Midazolam"  27  25  1.4  1.8  .6   .7     .  . . . "ACES"                                      "PANSS-EC"    
end

reshape long treatment n bm bsd ym ysd RatingScale, i(ID) j(seq)

Thanks for your help.

[Clyde Schechter]

Well, you made a good start. You just need to finish the job. Follow the command you show with:

rename (bm-ysd) score=
reshape long score, i(ID seq) j(scale) string

and I believe you'll have what you want.

[Victor Avila-Quintero]

Well, you made a good start. You just need to finish the job. Follow the command you show with:

rename (bm-ysd) score=
reshape long score, i(ID seq) j(scale) string

and I believe you'll have what you want.

Thank you Clyde for the prompt response. I need to apologize because just noticed I missed to include the score data for the second rating scale, thus that data was not taken into account when trying to fix the issue. Here is the complete example dataset.

* Example generated by -dataex-. For more info, type help dataex
clear
input float ID str26 treatment1 str27 treatment2 int(n1 n2) str83 RatingScale1 double(bm1 bm2 bsd1 bsd2 ym1 ym2 ysd1 ysd2) str152 RatingScale2 double(bm7 bm8 bsd7 bsd8 ym7 ym8 ysd7 ysd8)
 1 "Haloperidol"                "Haloperidol"              21  12 "Other: Five-point combativeness scale"        1    1   .    .     .  . . . ""                .    . .   .    .    .    .     .
 2 "Midazolam"                  "Haloperidol"              42  42 ""                                             .    .   .    .     .  . . . ""                .    . .   .    .    .    .     .
 3 "Droperidol"                 "Ziprasidone"              25  28 "Other: Altered Mental Status Scale (AMSS)"    3    3   .    .    -1 -3 . . "Other: "         .    . .   .    .    .    .     .
 4 "Droperidol"                 "Ziprasidone"              50  46 "Other: Altered Mental Status Scale (AMS)"  3.12 3.41   .    . -1.68 -2 . . ""                .    . .   .    .    .    .     .
 5 "Ketamine"                   "Haloperidol"              45  45 "Other: altered mental status score (AMSS)" 2.62 2.59 .94 1.13     .  . . . ""                .    . .   .    .    .    .     .
 6 "Haloperidol"                "Midazolam"                23  25 "Other: Restless score"                        .    .   .    .     .  . . . ""                .    . .   .    .    .    .     .
 7 "Olanzapine"                 "Haloperidol"              54  57 "Other: Agitation/Sedation Scale"           3.96 4.15   .    .     .  . . . ""                .    . .   .    .    .    .     .
 8 "Ketamine"                   "Haloperidol + Midazolam"  40  40 "RASS"                                      3.64 3.45   .    .     .  . . . ""                .    . .   .    .    .    .     .
 9 "Lorazepam"                  "Droperidol"              100 102 "Other: Sedation Scale"                      5.3  5.6  .7   .6     .  . . . ""                .    . .   .    .    .    .     .
10 "Haloperidol + Promethazine" "Olanzapine"              150 150 "Other: CGI-S"                              4.63 4.55 .72  .78     .  . . . "Other: CGI-I"    .    . .   . 1.33 1.69 .739 1.037
11 "Haloperidol + Promethazine" "Haloperidol + Midazolam"  27  25 "ACES"                                       1.4  1.8  .6   .7     .  . . . "PANSS-EC"     25.7 24.3 6 5.6 10.7  9.4  6.7   9.4
end

Again, I am sorry for the incomplete example on my previous post.

Thanks for the help.

[Clyde Schechter]

This is a bit more complicated than what was originally posed. First, there are some variables that get numbered 7 or 8, but not others. And now we have repeated values of RatingScale, which is a text variable. (Actually, this complication was present in the original data, in variable treatment, which I just ignored there.)

It is not possible to fully rework this into a doubly long layout, because we can't have text and numerical values in the same variable (unless we change the numeric variables to their string representations--but that's usually a bad idea to do.) But with that limitation, we can do this:

isid ID

ds *1
local stubs `r(varlist)'
local stubs `"`stubs' "'
local stubs: subinstr local stubs "1 " " ", all
display `"`stubs'"'

reshape long `stubs', i(ID) j(seq)
ds ID seq, not
local vbles `r(varlist)'
ds `vbles', has(type numeric)
local num_vars `r(varlist)'
ds `vbles', has(type string)
local str_vars `r(varlist)'
rename (`num_vars') score=
rename (`str_vars') content=
reshape long content score, i(ID seq) j(descriptor) string

That said, this double-long layout is useful for some limited purposes, such as doing calculations that range over all of the numeric scores. But that, in turn, only makes sense if these numeric scores are related to each other in such a way that it is meaningful to do such calculations. As their names are not informative, I can't tell if that is the case here or not. If not, or if you have no such calculations in mind, you would probably be better off with just the singly long layout that results after the first -reshape- command.