How to merge data when the data of the same family are scattered in different pens

[Ding-Dang Yang]

Hi Statalist,

I have some data as follows

id    year  population code  spouse population code
10201    2015    1    2
10201    2016    1    2
10202    2015    2    1
10202    2016    2    1
10301    2015    1    0
10301    2016    1    0
10401    2015    1    0
10401    2016    1    0
10402    2015    2    3
10402    2016    2    3
10403    2015    3    2
10403    2016    3    2

As you can see, each id will have two years of data
Each id has its own number in the family, and the number of the spouse in the family (spouse number 0 means no spouse)
The first three codes of the id are the family number. The same first three yards means they belong to the same family
How can I combine the same family scattered in several profiles into "one profile for one family"
thanks!

[Fei Wang]

Ding-Dang, it depends on what the other profiles look like. Displaying another data example which you'd like to merge with the one in #1 would be helpful.

[Ding-Dang Yang]

My expression may not be very clear, the complete data is as follows:

id  year  population code  spouse code  income
10201    2015    1    2    100000
10201    2016    1    2    100000
10202    2015    2    1    300000
10202    2016    2    1    400000
10301    2015    1    0    200000
10301    2016    1    0    300000
10401    2015    1    0    100000
10401    2016    1    0    200000
10402    2015    2    3    100000
10402    2016    2    3    300000
10403    2015    3    2    100000
10403    2016    3    2    100000

I hope this can be turned into something like the following

f_id   year   p_1  s_1     i_1   p_2  s_2     i_2   p_3 s_3     i_3
102    2015    1    2    100000   2    1    300000   .    .      .
102    2016    1    2    100000   2    1    400000   .    .      .
103    2015    1    0    200000   .    .       .     .    .      .
103    2016    1    0    300000   .    .       .     .    .      .
104    2015    1    0    100000   2    3    100000   3    2    100000
104    2016    1    0    200000   2    3    300000   3    2    100000

or other similar form
thanks!

[Fei Wang]

gen fid = int(id/100)
gen pid = id - fid*100
drop id

reshape wide pcode scode income, i(fid year) j(pid)