Hi I wondering if anyone could help me reshape this data set I'm currently working on.(Sample using dataex attached below).

The variable income_sources takes 16 possible values while memid*_new represents each individuals in the household. If, for a given household(say hh -1), a member (say memid - 4) earns A, B & C amounts of money from income_sources 2,11 & 13while another member (memid - 2) earns X & Y amounts of money from income_sources 4 & 5 the data would like(the rest of the members in the household don't earn any income) :

Under new_hhid, 1 appears five times, under income_sources we've 2,4,5,11,13 & under memid_1 we've A,B & C corresponding to income_sources 2,11 & 13 respectively while the rest( income_sources 4 & 5) are set to missing & for memid_2, we've X & Y corresponding to income_sources 4 & 5 & again, the rest set to missing.

Then comes hhid no. 2 & so on...

I would like the data to look like the following:

hhid mem income_source1 income_source2 income_source3 .......
1 1 x . y .......
1 2 . . z .......
2 1 a b .


& so on.

I've been trying to reshape this data for the past 24 hours, but given the bizarre nature of it, so far I've rather unsuccessful.

Any help / guidance would be really appreciated.

Thanks.



Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(state_id dist_id) int village_id str30 village_name long new_hhid float income_sources long(new_memid1 new_memid2 new_memid3 new_memid4 new_memid5 new_memid6 new_memid7) int new_memid8 long new_memid9 byte new_memid10 float uniqueid
10 14 34 "KITHAIPARA"  3    6 68000 . . . . . . . . . 55
10 14 34 "KITHAIPARA"  3 10.1  1200 . . . . . . . . . 55
10 14 34 "KITHAIPARA"  3   14  8758 . . . . . . . . . 55
10 14 36 "HARIHAR PUR" 4    3 25000 . . . . . . . . . 64
10 14 36 "HARIHAR PUR" 4   14 30106 . . . . . . . . . 64
end