Hi,
I am importing a household data from excel, and data set does not have a proper id variable, and I am trying to generate one. My data looks like this:
ID name title
1 John household head
. Mary wife
. Sam son
3 Chris household head
. Pat mother
3 Flo household head

Sometimes it is like:

14 Kerry Household head
2 Susan wife
. Christine daughter
(Kerry, Susan, Christine are one household)
I would like to convert one household to one line. I cannot use reshape because the id does not follow what stata allows. Can you please help?

Thank you!