Reshaping data from wide to long

[Lynne Millar]

When I try to reshape I keep getting this error r(111) no xij variables found
You typed something like reshape wide a b, i(i) j(j).
reshape looked for existing variables named a# and b# but could not find any. Remember this
picture:

I have tried entering all variables and also using a hyphen between the first and last. I have attached my data. Any help would be greatly appreciated.

[Clyde Schechter]

Although the information you provide is to scanty to support answering your question, the error message Stata gave you says that you ran a -reshape wide- command. But the title of your post suggests you want to reshape from wide to long. That calls for a -reshape long- command. If that doesn't resolve your problem, post back with better information.

Please read the Forum FAQ before you do that, with particular attention to #9 through #12. There you will learn, among other things, that attachments are deprecated here and should be avoided where alternatives exist. The most useful way to show example data is with the -dataex- command. In addition, you will see that showing the exact command you used to attempt your reshape is crucial to getting help. When you leave what you did to the imagination, the best you can hope for is imaginary help.

Please post back with the actual command you used, and example data using -dataex-. If you do that, I think you will get a timely and helpful response.

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.

[Lynne Millar]

reshape long surveydate1-CASQtot4, i(id) j(wave)

no xij variables found
You typed something like reshape wide a b, i(i) j(j).
reshape looked for existing variables named a# and b# but could not find any. Remember this
picture:

long wide
+---------------+ +------------------+
| i j a b | | i a1 a2 b1 b2 |
|---------------| <--- reshape ---> |------------------|
| 1 1 1 2 | | 1 1 3 2 4 |
| 1 2 3 4 | | 2 5 7 6 8 |
| 2 1 5 6 | +------------------+
| 2 2 7 8 |
+---------------+

long to wide: reshape wide a b, i(i) j(j) (j existing variable)
wide to long: reshape long a b, i(i) j(j) (j new variable)
r(111);

end of do-file

r(111);

[Lynne Millar]

Sample data - not srue if I have psoted this correctly? Any help would be great

* Example generated by -dataex-. For more info, type help dataex
clear
input long id float age1 int c_sex1 byte(CASQpos1 CASQpos2 CASQpos3 CASQpos4 CASQneg1 CASQneg2 CASQneg3 CASQneg4 CASQtot1 CASQtot2 CASQtot3 CASQtot4)
1000102        . 2 15 11 16 16  7  9  6  8  8  2 10  10
1000103        . 2 19 10 13 12  1  5  5  7 18  5  8   5
1000104        . 2 14 15 12 15  7  4  2  6  7 11 10   9
1000105        . 2 15 13 14 14  8  4  7  5  7  9  7   9
1000106        . 2 12 16 15 13  7  6  8 10  5 10  7   3
1000107        . 2 10 13 16 15  3  8 12  8  7  5  4   7
1000108 8.837782 2  9 14 15  . 12 15  8  . -3 -1  7   .
1000109 9.163587 2 17 21 22 22  2  3  4  3 15 18 18  19
1000110        . . 17 19 18 18  5  5 12  9 12 14  6   9
1000111        . . 12 16  .  . 10  5  .  .  2 11  .   .
1000113        . 1 10 10 14 17  4  8  6  4  6  2  8  13
1000114        . 1 19 19 20 24  5  3  1  1 14 16 19  23
1000115        . . 14  9  6  8  9 14 14  9  5 -5 -8  -1
1000116        . . 19 15 19 12  4  5  3  3 15 10 16   9
1000118        . 1 18 13 14 17  6  8  9 11 12  5  5   6
1000121 8.985626 1 13 13  .  .  7  6  .  .  6  7  .   .
1000122        . 1 19 14 14  .  2 11  9  . 17  3  5   .
1000123        . 1 12 11 10 15 10 10 10 12  2  1  0   3
1000201        . .  8 14 10  4 10 12 15 14 -2  2 -5 -10
1000202        . 2 11 12 13 15  8  9  8  9  3  3  5   6
1000203        . . 20 13 13 17  9 10 11  6 11  3  2  11
1000204        . 2 17 17 13  . 10  8 12  .  7  9  1   .
1000205        . . 16 16 17  .  7 10 12  .  9  6  5   .
1000206 8.914442 2 13 10  5  .  7  2  3  .  6  8  2   .
1000207        . 2 15 16 15 16  9  8  6  7  6  8  9   9
1000208        . 2 11 11 11 16  9 10  8 11  2  1  3   5
1000210        . . 13 15 13 11 14 15 13 12 -1  0  0  -1
1000211        . 1 17 12 18  9  3  2  7  3 14 10 11   6
1000212        . . 19 10 12  8 11 10  7  7  8  0  5   1
1000213        . . 13 11 11  8 14  5 16 11 -1  6 -5  -3
1000214        . . 13 10 12 14  9 10 16 16  4  0 -4  -2
1010101        . 2 19 18 16  .  6  7  9  . 13 11  7   .
1010102        . 1 15 19 19 16  7  5  5  8  8 14 14   8
1010104        . 2 11 14 11 12  7  8 15 13  4  6 -4  -1
1010105        . 2 16 18 16 19  3  3  2  3 13 15 14  16
1010106        . . 13  . 14  . 12  .  8  .  1  .  6   .
1010107        . 1 13 11  .  .  6  6  .  .  7  5  .   .
1010108        . 1 18 21 19 19  4  4  3  3 14 17 16  16
1010109        . 2 11 16 15 18 12 12  8  9 -1  4  7   9
1010110        . 2 15 16 16 17  4  3  6  4 11 13 10  13
1010111        . 1 16  . 13 14 12  .  7  4  4  .  6  10
1010112        . . 14 13 13 14 11  7  8 15  3  6  5  -1
1010113 8.651608 1 15 18 14 17 10  8  9  7  5 10  5  10
1010114        . . 20 17 20 20  3  1  4  4 17 16 16  16
1010115        . 2 12 12 14 11 14 10 10 13 -2  2  4  -2
1010116        . 2 10 15 18 15  5  4  5  4  5 11 13  11
1010117        . . 13 11 10 12 10 13 10 10  3 -2  0   2
1010118        . . 13 13 12  6  9  9 10 12  4  4  2  -6
1010119        . 2 13  9 11 16 11  9 10  5  2  0  1  11
1010120        . 2 13 16 18 12 10  4  4  6  3 12 14   6
1010121        . 2 15 14 14 16  9  7  6  3  6  7  8  13
1010122        . 1 11 11 13 17 13  8 11 11 -2  3  2   6
1010123        . . 16 15 15 12  6  6  5  8 10  9 10   4
1010124        . 2 10 12 15 15  4  5  8  4  6  7  7  11
1010125        . . 16 13  9 12  6 13 16  9 10  0 -7   3
1010126        . . 13 16 13 12  8  5 10  5  5 11  3   7
1010127        . 2 10 10 18 13 11  9  6  8 -1  1 12   5
1010128        . 1 16 15 17 11  8  3  4  5  8 12 13   6
1010129        . . 12 11 14 12  6  8 12 11  6  3  2   1
1010202        . 2 16  9 12 20  5 18 15  4 11 -9 -3  16
1010203        . 1 15 13 11 13  4 10  5  9 11  3  6   4
1010204        . 1 15  9 10 10  5  9  8  6 10  0  2   4
1010206        . 1 19 22 19 23  6  4  4  3 13 18 15  20
1010207        . 1 12 15 12 14  8  6  4  6  4  9  8   8
1010208        . 1 12 11 13  8  8  9 10  7  4  2  3   1
1010209        . 2 18 17 21 12 14  9  7  7  4  8 14   5
1010210        . . 17 11 19 14 10 11  7  7  7  0 12   7
1010211        . 1 14 15 15 19  8 12  8  5  6  3  7  14
1010212        . 2 15 21 21 20  6  4  5  8  9 17 16  12
1010214        . . 16 13 18 19  9 10  4  8  7  3 14  11
1010215        . 2 11  7  9  .  5  2  6  .  6  5  3   .
1010216        . 1 22 17 22 18  4  4  3  6 18 13 19  12
1010217 8.829569 2 10 15 13 20 12  3 13  2 -2 12  0  18
1010219 8.492813 2 15 12 19 13 11  8 12 10  4  4  7   3
1010220        . 1 10 11 19 13 10  5  3  5  0  6 16   8
1010301        . . 12 11 13 18  9 11  5  5  3  0  8  13
1010302        . 2 10 18 15 14  3  6  5  7  7 12 10   7
1010303        . 1 17 15 15 22  8  6  3  4  9  9 12  18
1010304        . 2 16 12 13 20  8  7 10  7  8  5  3  13
1010305  8.73922 2  9 17 16 12 13  8  8  2 -4  9  8  10
1010306        . 2 14 13 13 12 11 16 12  9  3 -3  1   3
1010307        . 2 17 17 20 20  5  5  6  9 12 12 14  11
1010308        . 1 16 12 10  .  7 11  9  .  9  1  1   .
1010309 9.500342 1 14  9 11 11  8  7 11  8  6  2  0   3
1010310        . 1 14 11 12 11 11 11 10 12  3  0  2  -1
1010311        . 2 11 14 13 16  8  8 12  7  3  6  1   9
1010312        . 2 14 18 13 20  4  4  3  4 10 14 10  16
1010313        . 2 13 16 16 16  5  4  7  8  8 12  9   8
1010314        . 1  6 13 10 12 11  8  9  9 -5  5  1   3
1010315        . 2 17 21 16 15  6  4  6  5 11 17 10  10
1010316        . 1 14 12 14 11  5  9  8 10  9  3  6   1
1010317        . 1 14 10  9 17 11 14 11  3  3 -4 -2  14
1010318        . 2 15 16 13  9 10  7  6  1  5  9  7   8
1010319        . 1 13 15 13 16  9 13 10  8  4  2  3   8
1010320        . . 11 12 15 13 10 14 12 12  1 -2  3   1
1010321        . 1 12 10 15 11  8  9  6  4  4  1  9   7
1010322 8.481862 2 13 17 18 15  8  6  7  5  5 11 11  10
1010323        . 1 15 17 13  .  9  8  9  .  6  9  4   .
1010325        . 1 11 15 14 10  7  5  8 10  4 10  6   0
1010326        . 1  9 14  9 15  9 10 10  3  0  4 -1  12
end
label values c_sex1 labels0
label def labels0 1 "male", modify
label def labels0 2 "female", modify

[Clyde Schechter]

reshape long age c_sex CASQpos CASQtot, i(id) j(wave)

The key to remember is that in -reshape long- (unlike -reshape wide-) you do not list variable names. (And that means you can't use * and - wildcards either.) You list the stubs that, when combined with the -j()- variable (wave, in this case) form the variable names.

There is a pretty steep learning curve for using -reshape-. After you do it enough times, though, one day it will just suddenly "click" in your head and you'll never get tripped up by this again.

Added: In writing this command, I assumed that the variables c_sex1 and age1 are parts of series like c_sex2 through c_sex4 and age1 through age4. Age, of course, usually will change from one wave of a survey to the next. But c_sex typically doesn't, so I wasn't sure if I handled this correctly. If c_sex1 is supposed to be a constant value across waves, just remove mention of c_sex from the -reshape- command, and the value of c_sex1 will then be applied to c_sex in all waves.