reshape data issue

[xin liang]

HI I am using stata to do relatives in families and use data below. you can see this example below.
Serial is the number for every house. for example: serial is 12, we can see in this house, there is a household and a spouse.
relate is the relations between household and other members in this house.
Foreignborn is a dummy variable means whether this observation is foreign born or not. If it is, the value will be 1.
So now i want to reshape my data. I need to put every information to the household in every house.
For example, in house 8, i need to move spouse and this spouse's foreignborn characteristic to the right side of household like table below. In house 12, i need to do same thing. So i can know whether this household has wife or not and whether his wife is foreign born or not.
I used reshape, but the error said the value of relate is not unique within serial, so i dont know how to solve with this issue.
Appreciated of your help.

year serial relate foreignborn 2010 1 Head/householder 1 2010 6 Head/householder 1 2010 7 Head/householder 1 2010 8 Head/householder 1 Spouse 1 2010 8 Spouse 1 2010 12 Head/householder 1 Spouse 1 2010 12 spouse 1 2010 14 Head/householder 1 2010 15 Head/householder 1 2010 17 Head/householder 1 2010 18 Head/householder 1 2010 19 Head/householder 1 2010 20 Head/householder 1 2010 22 Head/householder 1 Spouse 1 2010 22 spouse 1 2010 24 Head/householder 1 2010 25 Head/householder 1 2010 27 Head/householder 1 2010 29 Head/householder 1 Spouse 1 2010 29 spouse 1 2010 32 Head/householder 1 2010 34 Head/householder 1 Spouse 1 2010 34 spouse 1 2010 35 Head/householder 1 Spouse 1 2010 35 spouse 1 2010 36 Head/householder 1 Spouse 1 2010 36 spouse 1

[xin liang]

someone can help?

[Justin Niakamal]

Perhaps you can work off this.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year byte serial str16 relate byte foreignborn
2010  1 "Head/householder" 1
2010  6 "Head/householder" 1
2010  7 "Head/householder" 1
2010  8 "Head/householder" 1
2010  8 "Spouse"           1
2010 12 "Head/householder" 1
2010 12 "spouse"           1
2010 14 "Head/householder" 1
2010 15 "Head/householder" 1
2010 17 "Head/householder" 1
2010 18 "Head/householder" 1
2010 19 "Head/householder" 1
2010 20 "Head/householder" 1
2010 22 "Head/householder" 1
2010 22 "spouse"           1
2010 24 "Head/householder" 1
2010 25 "Head/householder" 1
2010 27 "Head/householder" 1
2010 29 "Head/householder" 1
2010 29 "spouse"           1
2010 32 "Head/householder" 1
2010 34 "Head/householder" 1
2010 34 "spouse"           1
2010 35 "Head/householder" 1
2010 35 "spouse"           1
2010 36 "Head/householder" 1
end

replace relate = strtoname(relate)
reshape wide foreignborn, i(serial year) j(relate)string

help dataex