Simple advice on difficulty reshaping from long to wide please

[jemima spencer]

Could someone possibly help me with merging some data from long to wide?

Apologies for not using dataex - my University is having problems with their STATA licence currently.

I have a dataset in long format, with multiple creatinine test results ("creatinine") for each person (ID). Each test result is associated with a time in days ("timetocreat"), and on occasion there are multiple test results for the same ID at the same time (such that "ID" and "timetocreat" are similar but "creatinine" is different). To try to overcome this I created an additional "obs no" which is distinct to each line of data.

Example here:

ID	Timetocreat	Creatinine	Obs no
1	0	115	1
1	1	110	2
2	0	256	3
2	0	280	4
2	1	310	5
2	8	420	6
3	0	86	7

What I would like is a single line of data per ID, with all the creatinine values, and associated times, on that single line.

I have (most lately!) tried this command as follows:

reshape wide creatinine timetocreat, i(ID) j (obs_no)

However I receive an error command saying there are too many distinct observations for "obs no".


Could someone kindly point out where I am going wrong?

Thankyou

Jemima Scott

[William Lisowski]

Your obs_no should restart at 1 for each new ID.

* Example generated by -dataex-. For more info, type help dataex
clear
input int(ID timetocreat creatinine)
1 0 115
1 1 110
2 0 256
2 0 280
2 1 310
2 8 420
3 0  86
end
sort ID, stable 
by ID: generate obs_no = _n
list, sepby(ID) noobs
reshape wide creatinine timetocreat, i(ID) j(obs_no)
list, clean noobs

. list, sepby(ID) noobs

  +-----------------------------------+
  | ID   timeto~t   creati~e   obs_no |
  |-----------------------------------|
  |  1          0        115        1 |
  |  1          1        110        2 |
  |-----------------------------------|
  |  2          0        256        1 |
  |  2          0        280        2 |
  |  2          1        310        3 |
  |  2          8        420        4 |
  |-----------------------------------|
  |  3          0         86        1 |
  +-----------------------------------+

. list, clean noobs

    ID   timeto~1   creati~1   timeto~2   creati~2   timeto~3   creati~3   timeto~4   creati~4  
     1          0        115          1        110          .          .          .          .  
     2          0        256          0        280          1        310          8        420  
     3          0         86          .          .          .          .          .          .

Having told you how to do that, I am now going to tell you that it is a bad idea.

The experienced users here generally agree that, with few exceptions, Stata makes it much more straightforward to accomplish complex analyses using a long layout of your data rather than a wide layout of the same data. You should try to achieve what you need with the data organized as it currently is, and seek the help of Statalist in doing so. The sort of problems you will encounter trying to use your wide data will almost certainly be solved by reshaping the data back to long. It is much easier, for example, to compare the second observation to the first, the third to the second, and so on, than it is to compare the second variable to the first, the third to the second, etc.

[jemima spencer]

Thank you so much.

Silly me, I had an "obs id" that did just this, and decided it was obsolete so deleted it.

However, thanks for the additional advice. I've been used to working in wide format so assumed this was best/easiest etc, but will start doing some reading about how to work with it in long format. Such a lot to learn(!)

Thanks again