problems with transposing from long to wide

[Gianfranco Di Gennaro]

Dear all,

I have a problem with transposing data from long to wide.

I'm doing a survival analysis with time-dependent variables.
I want to do multiple imputation of missing data, and for this I have to switch to wide data.

I have patients with different follow-up times and data are organized as follows:

id	covariate	covariate	time	event
1	0	0	6	0
1	0	1	10	0
1	1	1	12	0
1	0	0	18	1
2	1	1	1	0
2	1	1	3	0
2	1	0	5	0
2	0	0	7	0
2	1	1	8	0
2	0	1	10	1

Unfortunately when I use the "transpose" command I get a very strange error:

covariate2.066666603088379 invalid variable name

The strange fact is that the covariate has a "normal" name (a binary variable named "EventiYN")

Does anybody know how to fix this?

[Clyde Schechter]

You cannot transpose this data set because the first column must be a string variable in order to do that. Also, Stata does not have a "transpose" command. There is a command that does transposition, but its name is -xpose-. Perhaps you used that? In any case, what you actually need to do is -reshape- it.

Also what you show in our tableau is not a possible extract of a Stata data set because you cannot have two variables both named covariate. On the assumption that in your real data set these variables are actually named covariate_a and covariate_b, the -reshape- would go something like this.

reshape wide covariate_a covariate_b event, i(id) j(time)

Now, this code will not work if time contains values that are not positive integers.

Anyway, if you encounter problems getting the -reshape- command to work, post back with a usable and correct example from your actual Stata data set. The way to do that is by using the -dataex- command. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Gianfranco Di Gennaro]

Dear Clyde, thanks for your message.

I used "reshape", I'm sorry.

Please find the following dataex.

And anyway, yes...I have times that are no integer values!

I'll search how to round them to integer

* Example generated by -dataex-. For more info, type help dataex
clear
input float(id time OutYN) int age byte sex int months byte(patho drugpre drug concobas outcome conco CBZ_ PHT_ PHB_ PRM_ eventicot Eventi Dropout_Y_N) float dicOutcome
 1         6 1 39 0  60 1 3 5 2 1 2 0 0 0 0 0 . 0 0
 1        12 1 39 0  60 1 3 5 2 2 2 0 0 0 0 0 . 0 0
 1 12.866667 1 39 0  60 1 3 5 2 2 2 0 0 0 0 0 . 0 0
 2         6 1 28 1 232 1 5 1 2 2 2 0 0 0 0 0 . 0 0
 2        12 1 28 1 232 1 5 1 2 3 2 0 0 0 0 0 . 0 1
 2 20.766666 1 28 1 232 1 5 1 2 3 2 0 0 0 0 0 . 0 1
 3         6 1 31 1 252 1 5 1 2 1 2 1 0 0 0 0 . 0 0
 3        12 1 31 1 252 1 5 1 2 2 2 1 0 0 0 0 . 0 0
 3      21.3 1 31 1 252 1 5 1 2 2 2 1 0 0 0 0 . 0 0
 4         6 1 31 1 252 3 5 3 1 1 1 0 0 0 0 0 . 0 0
 4        12 1 31 1 252 3 5 3 1 5 1 0 0 0 0 0 . 0 1
 4      14.2 1 31 1 252 3 5 3 1 5 1 0 0 0 0 0 . 0 1
 5         6 1 21 0  72 1 5 1 2 1 2 0 0 0 0 0 . 0 0
 5        12 1 21 0  72 1 5 1 2 1 2 0 0 0 0 0 . 0 0
 5        24 1 21 0  72 1 5 1 2 1 2 0 0 0 0 0 . 0 0
 5 33.466667 1 21 0  72 1 5 1 2 1 2 0 0 0 0 0 . 0 0
 6         6 1 28 1 264 1 5 1 2 2 2 0 0 0 0 0 . 1 0
 6        12 1 28 1 264 1 5 1 2 2 2 0 0 0 0 0 . 1 0
 6        20 0 28 1 264 1 5 1 2 2 2 0 0 0 0 0 . 1 0
 7         6 1 27 1 156 1 5 1 1 2 1 0 0 0 0 0 . 0 0
 7        12 1 27 1 156 1 5 1 1 3 1 0 0 0 0 0 . 0 1
 7        24 1 27 1 156 1 5 1 1 3 1 0 0 0 0 0 . 0 1
 7 33.466667 1 27 1 156 1 5 1 1 3 1 0 0 0 0 0 . 0 1
 8         6 1 30 1 240 3 4 3 1 1 1 0 0 0 0 1 3 0 0
 8        12 1 30 1 240 3 4 3 1 1 1 0 0 0 0 1 3 0 0
 8 33.466667 1 30 1 240 3 4 3 1 1 1 0 0 0 0 1 3 0 0
 9         6 1 31 0 132 1 5 1 1 2 1 1 0 0 0 0 . 0 0
 9        12 1 31 0 132 1 5 1 1 3 1 1 0 0 0 0 . 0 1
 9        24 1 31 0 132 1 5 1 1 5 1 1 0 0 0 0 . 0 1
 9 33.466667 1 31 0 132 1 5 1 1 5 1 1 0 0 0 0 . 0 1
10         6 1 34 1  84 1 3 2 2 3 2 0 0 0 0 1 1 0 1
10        12 1 34 1  84 1 3 2 2 5 2 0 0 0 0 1 1 0 1
10        24 1 34 1  84 1 3 2 2 5 1 0 0 0 0 1 1 0 1
10      44.6 1 34 1  84 1 3 2 2 5 1 0 0 0 0 1 1 0 1
11         6 1 33 0 192 1 5 2 2 2 2 0 0 1 0 0 . 0 0
11        12 1 33 0 192 1 5 2 2 3 2 0 0 1 0 0 . 0 1
11        24 1 33 0 192 1 5 2 2 3 2 0 0 1 0 0 . 0 1
11  43.66667 1 33 0 192 1 5 2 2 3 2 0 0 1 0 0 . 0 1
12         6 1 50 0 480 1 3 2 2 3 1 0 0 0 0 0 . 0 1
12        12 1 50 0 480 1 3 2 2 5 1 0 0 0 0 0 . 0 1
12        24 1 50 0 480 1 3 2 2 4 2 0 0 0 0 0 . 0 1
12  45.63334 1 50 0 480 1 3 2 2 4 2 0 0 0 0 0 . 0 1
13         6 1 40 1  96 1 3 2 1 3 . . . . . 0 . 1 1
13        12 0 40 1  96 1 3 2 1 3 . . . . . 0 . 1 1
14         6 1 56 0 456 3 2 2 2 2 . . . . . 0 . 0 0
14       9.1 1 56 0 456 3 2 2 2 2 . . . . . 0 . 0 0
15         6 1 41 1  80 1 4 1 2 3 2 0 0 0 0 0 . 0 1
15        12 1 41 1  80 1 4 1 2 5 2 0 0 0 0 0 . 0 1
15        24 1 41 1  80 1 4 1 2 5 2 0 0 0 0 0 . 0 1
15  45.56667 1 41 1  80 1 4 1 2 5 2 0 0 0 0 0 . 0 1
16         6 1 22 1 240 3 5 1 3 2 2 0 0 0 0 0 1 0 0
16        12 1 22 1 240 3 5 1 3 3 2 0 0 0 0 1 1 0 1
16        24 1 22 1 240 3 5 1 3 2 2 0 0 0 0 1 1 0 0
16      44.6 1 22 1 240 3 5 1 3 2 2 0 0 0 0 1 1 0 0
17         6 1 58 1  96 1 3 1 2 3 1 0 0 0 0 1 1 0 1
17        12 1 58 1  96 1 3 1 2 5 1 0 0 0 0 0 1 0 1
17        24 1 58 1  96 1 3 1 2 5 1 0 0 0 0 0 1 0 1
17  45.63334 1 58 1  96 1 3 1 2 5 1 0 0 0 0 0 1 0 1
18         6 1 38 1 384 1 4 1 1 3 1 0 0 0 0 0 . 0 1
18        12 1 38 1 384 1 4 1 1 3 1 0 0 0 0 0 . 0 1
18        24 1 38 1 384 1 4 1 1 4 1 0 0 0 0 0 . 0 1
18  45.63334 1 38 1 384 1 4 1 1 4 1 0 0 0 0 0 . 0 1
19         6 1 22 0  24 1 2 3 1 5 1 0 0 0 0 0 . 0 1
19        12 1 22 0  24 1 2 3 1 5 1 0 0 0 0 0 . 0 1
19        24 1 22 0  24 1 2 3 1 5 1 0 0 0 0 0 . 0 1
19 33.466667 1 22 0  24 1 2 3 1 5 1 0 0 0 0 0 . 0 1
20         6 1 18 1  36 1 4 3 2 5 1 0 0 0 0 0 . 0 1
20        12 1 18 1  36 1 4 3 2 5 1 0 0 0 0 0 . 0 1
20        24 1 18 1  36 1 4 3 2 5 1 0 0 0 0 0 . 0 1
20  45.43333 1 18 1  36 1 4 3 2 5 1 0 0 0 0 0 . 0 1
21         6 1 20 1  36 1 2 3 1 3 1 0 0 0 0 0 . 0 1
21        12 1 20 1  36 1 2 3 1 4 1 0 0 0 0 0 . 0 1
21        24 1 20 1  36 1 2 3 1 5 1 0 0 0 0 0 . 0 1
21 33.266666 1 20 1  36 1 2 3 1 5 1 0 0 0 0 0 . 0 1
22         6 1 29 0  48 1 3 3 1 3 1 0 0 0 0 0 1 0 1
22        12 1 29 0  48 1 3 3 1 4 1 0 0 0 0 1 1 0 1
22        24 1 29 0  48 1 3 3 1 4 1 0 0 0 0 1 1 0 1
22 33.466667 1 29 0  48 1 3 3 1 4 1 0 0 0 0 1 1 0 1
23         6 1 59 1 120 1 2 3 1 5 1 1 0 0 0 0 . 0 1
23        12 1 59 1 120 1 2 3 1 5 1 1 0 0 0 0 . 0 1
23        24 1 59 1 120 1 2 3 1 5 1 1 0 0 0 0 . 0 1
23  45.63334 1 59 1 120 1 2 3 1 5 1 1 0 0 0 0 . 0 1
24         6 1 19 0  30 2 2 3 1 5 1 0 0 0 0 0 . 0 1
24        12 1 19 0  30 2 2 3 1 5 1 0 0 0 0 0 . 0 1
24        24 1 19 0  30 2 2 3 1 5 1 0 0 0 0 0 . 0 1
24 33.466667 1 19 0  30 2 2 3 1 5 1 0 0 0 0 0 . 0 1
25         6 1 43 1 432 1 4 3 1 1 1 0 0 0 0 0 . 1 0
25        12 1 43 1 432 1 4 3 1 1 1 0 0 0 0 0 . 1 0
25        13 0 43 1 432 1 4 3 1 1 1 0 0 0 0 0 . 1 0
26         6 1 40 0 204 1 2 3 1 1 1 0 0 1 0 1 1 1 0
26        12 1 40 0 204 1 2 3 1 1 1 0 0 1 0 1 1 1 0
26        13 0 40 0 204 1 2 3 1 1 1 0 0 1 0 1 1 1 0
27         6 1 28 1 120 2 2 3 1 2 1 0 0 0 0 0 . 0 0
27        12 1 28 1 120 2 2 3 1 4 1 0 0 0 0 0 1 0 1
27        24 1 28 1 120 2 2 3 1 3 1 0 0 0 0 1 1 0 1
27  45.43333 1 28 1 120 2 2 3 1 3 1 0 0 0 0 1 1 0 1
28         6 1 29 1 273 2 2 3 1 5 1 0 0 0 0 0 . 0 1
28        12 1 29 1 273 2 2 3 1 5 1 0 0 0 0 0 . 0 1
28        24 1 29 1 273 2 2 3 1 5 1 0 0 0 0 0 . 0 1
28 33.466667 1 29 1 273 2 2 3 1 5 1 0 0 0 0 0 . 0 1
end
label values drug AED_aggiuntol
label def AED_aggiuntol 1 "Perampanel", modify
label def AED_aggiuntol 2 "Eslicarbazepine", modify
label def AED_aggiuntol 3 "Lacosamide", modify
label def AED_aggiuntol 5 "Brivaracetam switch", modify

[Clyde Schechter]

So, a number of your variables are constant attributes of id, which makes life a bit simpler. The fact that time takes on non-integer values and is also not the same for all id's makes life a bit harder.

by id (time), sort: gen t = _n
reshape wide time OutYN outcome conco eventicot Eventi dicOutcome, i(id) j(t)

will give you the wide layout you need.

[Gianfranco Di Gennaro]

It works (I had no doubts!).
Dear Clyde, thank you very much.