How to convert a dataset from long to wide format?

[Hamza Khawar]

Hi Statalist,

I have a dataset in the long format which I am trying to convert into a wide format. The aim of this step is to get the categories column which has 15 unique values ( Publishing, Food, Music, etc) into variable columns containing observations from the variables "num_camp" and "num_succ" (each category value would have 2 columns). Can someone guide me how I can use the reshape wide command to do this?
Thanks

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year str12 category str2 state str31 city long(num_camp num_succ)
2014 "Publishing"   "PA" "Aaronsburg"              1 1
2014 "Food"         "SC" "Abbeville"               1 0
2014 "Music"        "SC" "Abbeville"               1 0
2014 "Technology"   "WA" "Aberdeen"                2 0
2010 "Film & Video" "SD" "Aberdeen"                1 0
2012 "Film & Video" "SD" "Aberdeen"                1 0
2014 "Publishing"   "SD" "Aberdeen"                1 0
2013 "Comics"       "NJ" "Aberdeen"                1 1
2014 "Music"        "SD" "Aberdeen"                1 1
2014 "Publishing"   "MD" "Aberdeen"                1 0
2011 "Film & Video" "SD" "Aberdeen"                1 0
2014 "Food"         "SD" "Aberdeen"                1 1
2013 "Music"        "NC" "Aberdeen"                1 1
2014 "Games"        "MD" "Aberdeen Proving Ground" 1 1
2013 "Games"        "MD" "Aberdeen Proving Ground" 1 0
2014 "Fashion"      "TX" "Abilene"                 2 0
2014 "Crafts"       "TX" "Abilene"                 1 1
2014 "Food"         "TX" "Abilene"                 1 0
2014 "Technology"   "TX" "Abilene"                 1 0
2014 "Music"        "TX" "Abilene"                 3 1
2014 "Games"        "TX" "Abilene"                 1 0
2011 "Photography"  "TX" "Abilene"                 1 0
2011 "Publishing"   "TX" "Abilene"                 1 0
2013 "Technology"   "KS" "Abilene"                 1 0
2013 "Publishing"   "TX" "Abilene"                 1 1
2013 "Music"        "TX" "Abilene"                 1 0
2014 "Technology"   "KS" "Abilene"                 1 0
2014 "Film & Video" "TX" "Abilene"                 1 0
2012 "Music"        "TX" "Abilene"                 1 1
2014 "Photography"  "TX" "Abilene"                 1 0
end

[Abdul Adam]

Hamza: is this what you want?

reshape wide num_camp num_succ, i(year state city) j( category ) string

[Nick Cox]

Abdul has the right idea -- so long as you fix the punctuation first, as spaces and ampersands won't be accepted in variable names.

replace category = subinstr(category, " & ", "_", .)

[Abdul Adam]

Thanks Nick. I should have looked at the variable names carefully.

[Clyde Schechter]

More generally,

replace category = strtoname(category)

will handle any characters that are not allowed in variable names, not just ampersands.

[Hamza Khawar]

I was trying the same code Abdul suggested but I was not getting the result which I now understand was because of the special characters in the variable. Thank you everyone for the answers.

[Nick Cox]

Fine, but better to say what you are trying and what happened. The answer would have been the same, perhaps just delivered a little more quickly.