Reshaping Long to Wide when the ID is Not Unique

[Remmy Saimon]

Hi Everyone,

I have Stacked on how to manipulate my data to make the Reshape Command Works. In my case I want to use i(y2_hhid) and j(zaocode) in a reshape command. However the zaocode - identifier for the crop appeared twice ( quantity harvested in farm "MI" and "M2") in the same household ( y2_hhid) and therefore not unique. See for example: household "0101014002029701" , zaocode 11 appeared twice with quantity 750 and 200 that comes from farm "M1" and "M2" respectively. When I try to reshape thee data to wide, stata gives me an error message that the j(zaocode) is not Unique.

Is there a way I can do to reshape the data and get accurate value of the total quantity of each product in each household?

note: the zaocode has missing values which I drooped them before running a reshape wide command.

Kindly Assist.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str16 y2_hhid int zaocode long(ag4a_15 ag4a_16) str3 plotnum
"0101014002017101" 13  200   30000 "M1"
"0101014002017101" 14  240   36000 "M1"
"0101014002017101" 12  100   30000 "M2"
"0101014002028401" 11  220   38500 "M1"
"0101014002028401" 41  100   14000 "M1"
"0101014002029701" 11  750  157500 "M1"
"0101014002029701" 11  200   42000 "M2"
"0101014002040901" 14  360   63000 "M1"
"0101014002040901" 34   40   40000 "M1"
"0101014002040901" 36   40   32000 "M1"
"0101014002040901" 11  108   21000 "M2"
"0101014002040901" 41  420   87500 "M2"
"0101014002047101" 13  100   18000 "M1"
"0101014002047101" 14  200   36000 "M1"
"0101014002055101" 14  500   50000 "M1"
"0101014002055101" 34   80   60000 "M1"
"0101014002055101" 34   20   15000 "M2"
"0101014002055101" 41  420   87500 "M2"
"0101014002076101" 11   60    9000 "M1"
"0101014002076101" 11  120   18000 "M2"
"0101014002076101" 41  120   18000 "M2"
"0101014002076101"  .    .       . "M3"
"0101014002076201" 11  100   15000 "M1"
"0101014002076201" 34  840  126000 "M1"
"0101014002076201" 34  160    2700 "M2"
"0102003003000401" 11  480   72000 "M1"
"0102003003000401" 11  600   90000 "M2"
"0102003003000401" 13  240   24000 "M3"
"0102003003002201" 13  260   65000 "M1"
"0102003003002201" 13  360   90000 "M2"
"0102003003002201" 13 1800  450000 "M3"
"0102003003002201" 32  240   96000 "M3"
"0102003003002201" 43  240  200000 "M3"
"0102003003014001" 11  200   40000 "M1"
"0102003003014001"  .    .       . "M2"
"0102003003016101" 11  300   54000 "M1"
"0102003003016101" 13  200   36000 "M1"
"0102003003016101" 11  300   54000 "M2"
"0102003003016101" 43   60   36000 "M2"
"0102003003017401" 11 2160  480000 "M1"
"0102003003017401" 34  140   84000 "M1"
"0102003003017401" 11 1728  384000 "M2"
"0102003003017401" 11 2700  675000 "M3"
"0102003003017401" 31  160  128000 "M4"
"0102003003017401" 11 1296  324000 "M5"
"0102003003017401" 43 1200  420000 "M5"
"0102003003020001"  .    .       . "M1"
"0102003003020001" 11 2000  280000 "M2"
"0102003003043001" 11  400  120000 "M1"
"0102003003043001" 21    .       . "M1"
"0102003003043001" 11  600  180000 "M2"
"0102003003043001" 21    .       . "M2"
"0102003003043001" 11  200   60000 "M3"
"0102003003043001"  .    .       . "M4"
"0102003003043001" 11  300   90000 "M5"
"0102003003043001" 31  100  108000 "M5"
"0102003003043001" 11  200   60000 "M6"
"0102003003043001" 31  200  200000 "M7"
"0102003003047901" 11  240   48000 "M1"
"0102003003047901" 41   60    9000 "M1"
"0102003003047901" 11  320   64000 "M2"
"0102003003047901" 13   88   14000 "M2"
"0102017003000101" 13  120   18000 "M1"
"0102017003000101" 13  160   24000 "M2"
"0102017003000101" 14  180   27000 "M3"
"0102017003000101" 43  300   30000 "M4"
"0102017003000101" 13  160   24000 "M5"
"0102017003000104" 13  400   84000 "M1"
"0102017003000104" 41  225   90000 "M1"
"0102017003000104" 43  150  150000 "M1"
"0102017003001701" 11  400   96000 "M1"
"0102017003001701" 41   60   12000 "M1"
"0102017003001701" 42  100   76000 "M1"
"0102017003002201" 13  600  150000 "M1"
"0102017003004801" 13  480   96000 "M1"
"0102017003004801" 11  240   48000 "M2"
"0102017003004801" 13  160   32000 "M2"
"0102017003004801" 32 1900  160000 "M2"
"0102017003004801" 11  360   72000 "M3"
"0102017003004801" 43  660  200000 "M3"
"0102017003010001" 13  800  168000 "M1"
"0102017003010001" 14 4800 1008000 "M1"
"0102017003024101" 13  240   36000 "M1"
"0102017003024101" 32   80   40000 "M1"
"0102017003024101" 13  600   90000 "M2"
"0102017003024101" 32   40   10000 "M2"
"0102017003024101" 41  120   36000 "M2"
"0102017003024601" 13  120   12000 "M1"
"0102017003024601" 32   40   20000 "M1"
"0102017003024601" 13  360   36000 "M2"
"0103013004016101" 11  150   37000 "M1"
"0103013004016101"  .    .       . "M2"
"0103013004016101"  .    .       . "M3"
"0103013004016101"  .    .       . "M4"
"0103013004021901" 43  100   36000 "M1"
"0103013004021901" 11  180   30000 "M2"
"0103013004021901" 13  360   54000 "M3"
"0103013004021901" 13  100   15000 "M4"
"0103013004025901" 11   40    8000 "M1"
"0103013004025901" 43    .       . "M1"
end
label values zaocode zaocode
label def zaocode 11 "Maize", modify
label def zaocode 12 "Paddy", modify
label def zaocode 13 "Sorghum", modify
label def zaocode 14 "Bulrush Millet", modify
label def zaocode 21 "Cassava", modify
label def zaocode 31 "Beans", modify
label def zaocode 32 "Cowpeas", modify
label def zaocode 34 "Pigeon pea", modify
label def zaocode 36 "Bambara nuts", modify
label def zaocode 41 "Sunflower", modify
label def zaocode 42 "Sesame", modify
label def zaocode 43 "Groundnut", modify

[Clyde Schechter]

Sure, you just have to do the totals first and then -reshape-

drop if missing(zaocode)

collapse (sum) ag4a_*, by(y2_hhid zaocode)
rename ag4a_* =_
reshape wide ag4a*, i(y2_hhid) j(zaocode)

That said, are you sure you want to do this? The resulting data set is going to have a huge number or variables, and there are only a few types of things that Stata does best with wide data. Any additional analysis you plan is likely to be easier if you just stop after the -collapse- and leave the data in long layout. What you are creating with wide layout is easier for the human eye to read, but you are likely to find it difficult or impossible to work with in Stata. Think it over.

[Remmy Saimon]

Thank you @ Clyde Scheechter. It worked.
Yes, Actually my focus is only on the few variables.