reshape long to wide format

[Muhammad Faisal]

I am stuck at this point. I would like to transform input data (long format) to wide format (output needed).

****Input data****
clear
input id str5 medicine
1 "med1"
1 "med2"
1 "med5"
1 "med6"
1 "med8"
2 "med1"
2 "med1"
2 "med3"
2 "med4"
2 "med10"
end

list

*****Output needed****
clear
input id med1 med2 med3 med4 med5 med6 med8 med10
1 1 1 0 0 1 1 1 0
2 2 0 1 1 0 0 0 1
end

list

I tried to find a unique identifier within a patient's id using _n or _N. It does not work. I do know what I am missing here.

bysort id medicine: gen subindex=_n
bysort id medicine: gen submax=_N

Any help in this regards highly appreciated.

Thank you

[Nick Cox]

You need to do a bit of work before and after you reshape, so far as I can see. In particular, there is no sense in which reshape knows about counting observations and those medicines not prescribed are all implicit.

****Input data****
clear
input id str5 medicine
1 "med1"
1 "med2"
1 "med5"
1 "med6"
1 "med8"
2 "med1"
2 "med1"
2 "med3"
2 "med4"
2 "med10"
end

gen work = real(substr(medicine, 4, .))
drop medicine
contract id work, freq(med) zero 
reshape wide med , i(id) j(work)

list

     +-------------------------------------------------------------+
     | id   med1   med2   med3   med4   med5   med6   med8   med10 |
     |-------------------------------------------------------------|
  1. |  1      1      1      0      0      1      1      1       0 |
  2. |  2      2      0      1      1      0      0      0       1 |
     +-------------------------------------------------------------+

[Muhammad Faisal]

Thank you Nick! This is really useful.

One more question is about medicine names. As in my original data, there are medicine names (which are String). Is there any way to use medicine names as variable names (instead of med1,med2,...).

[Nick Cox]

Yes; you can do that. Just give a data example.

[Muhammad Faisal]

Something like this.

****Input data****
clear
input id str20 medicine
1 "Vicodin"
1 "Simvastatin"
1 "Lisinorpril"
1 "Levothroxine"
1 "Azithromycin"
2 "Vicodin"
2 "Vicodin"
2 "Simvastatin"
2 "Lipitor"
2 "Amlodipine"
end

 *****Output needed****
clear
input id Vicodin Simvastin Lisinorpril Azithromycin Lipitor Amlodipine
1 1 1 1 1 0 0
2 2 1 0 0 1 1
end  
list

[Nick Cox]

****Input data****
clear
input id str20 medicine
1 "Vicodin"
1 "Simvastatin"
1 "Lisinorpril"
1 "Levothroxine"
1 "Azithromycin"
2 "Vicodin"
2 "Vicodin"
2 "Simvastatin"
2 "Lipitor"
2 "Amlodipine"
end

gen work = strtoname(medicine)
contract id  work, freq(med) zero 
reshape wide med , i(id) j(work) string 
rename med* * 
list

[Muhammad Faisal]

Thank you very much!