Hello, I am having trouble reshaping my data as it will require something beyond the usual long to wide format. My dataset is a relationship matrix for each person in a household across 50 years. More specifically, there is a row for every person-to-person relationship in each year. However, I would like it to be in person-year format with variables for the ID of all relationships in that year on the same row.

Note that "altid" refers to the ID of the other person in that specific relationship. For example, ID 124 may be ID 123's father, and IDs 125 and 126 may be ID 123's siblings. Furthermore, you will notice that the "rel" variable (i.e., relationship to the respondent) is 55 for each row. That is intentional and due to subsetting.

I have subsetted the data as needed and have about 555,500 rows. Please note that not everyone has the same number of relationships and that the same person may have a different number of relationships at a different year. There are no missing data.

My data looks as follows:

Code:
clear 
input int(id year altid rel)
123 1968 124 55
123 1968 125 55
123 1968 126 55
123 1968 127 55
345 1968 346 55
345 1968 347 55
345 1968 348 55
567 1968 568 55
567 1968 569 55
123 1969 124 55
123 1969 125 55
123 1969 127 55
345 1969 346 55
345 1969 348 55
345 1969 349 55
567 1969 568 55
567 1969 569 55
567 1969 570 55
567 1969 571 55
567 1969 572 55
567 1969 573 55
end

I would like the data to look like this:

Code:
input int(id year altid1 altid2 altid3 altid4 altid5 altid6 rel)
123 1968 124 125 126 127 . . 55
123 1969 124 125 127 . . . 55
345 1968 346 347 348 . . . 55
345 1969 346 348 349 . . . 55
567 1968 568 569 . . . . 55
567 1969 568 569 570 571 572 573 55
end
To be clear, I have looked a number of threads with similar circumstances but none seem to quite match my situation. Accordingly, I am at a bit of a loss. Any advice would be sincerely appreciated!