Reshape Wide Accouting for Row duplicates

[Viktor Johanson]

Hi everyone, I have a problem when using the reshape wide command. In the example provided below I want to use the ID as an identifier, so that STATA generates data in wide format with "code" generated as Code 1, Code 2 etc for each unique code, but the ID is to mentioned only once. As for now I have "gen rowunique=_n" so that each row has a unique number. Not provided in the dataset below is "OnOff" which varies between 1 and 2, which is used in "j" in the reshape wide command. I then use reshape wide ID Code , i(rowunique) j(OnOff). However, this doesn't combine the data into one row as I would like it to. * Example generated by -dataex-. For more info, type help dataex clear input byte id str4(code) 1 "X001" 1 "X002" 1 "T004" 2 "X005" 2 "R001" end Your help is very much appreciated! Regards, Viktor

[Andrew Musau]

Your identifiers are mixed up. Try:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id str4 code
1 "X001"
1 "X002"
2 "X005"
end

gen obsno=_n
reshape wide code, i(id) j(obsno)

Res.:

. l

     +----------------------------+
     | id   code1   code2   code3 |
     |----------------------------|
  1. |  1    X001    X002         |
  2. |  2                    X005 |
     +----------------------------+

You could also number codes within id:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id str4 code
1 "X001"
1 "X002"
2 "X005"
end

gen obsno=_n
bys id (obsno): replace obsno=_n
reshape wide code, i(id) j(obsno)

Res.:

. l

     +--------------------+
     | id   code1   code2 |
     |--------------------|
  1. |  1    X001    X002 |
  2. |  2    X005         |
     +--------------------+

[Nick Cox]

Does this help?

* Example generated by -dataex-. For more info, type help dataex 
clear 
input byte id str4(code) 
1 "X001" 
1 "X002" 
1 "T004" 
2 "X005" 
2 "R001" 
end

set seed 42

gen whatever = runiformint(1, 5)
gen newid = 1 if id != id[_n-1]
replace newid = newid[_n-1] + 1 if missing(newid)

reshape wide code whatever, i(id) j(newid)

list 

     +-------------------------------------------------------------+
     | id   code1   whatev~1   code2   whatev~2   code3   whatev~3 |
     |-------------------------------------------------------------|
  1. |  1    X001          2    X002          1    T004          5 |
  2. |  2    X005          3    R001          3                  . |
     +-------------------------------------------------------------+

[Viktor Johanson]

As always, thanks for the quick reply Nick!

Applying the command I get "newid not unique within ID". Do I have to apply the rowunique in "i()"? Or does the "runiformint" need to be set differently? The ID in the dataset goes between 0-1700000.

[Viktor Johanson]

Went with Andrews example and I think it works! Thanks!
gen obsno=_n bys id (obsno): replace obsno=_n reshape wide code, i(id) j(obsno)

[Nick Cox]

For my code you may well need

gen long newid = 1

with a very large dataset, but I am still puzzled at the report.

runiformint() is irrelevant to what you need. I used it just to create a variable as extra, given that you did not supply a suitably detailed data example.