How to create a variable from values of other members of a group?

[Guest]

Dear all,

I am analysing an unbalanced panel dataset that is similar to the following example:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(family id) str1 partnerid int(year inc)
1 1 "2" 2000 1250
1 1 "2" 2001 1250
1 1 "2" 2002 1300
1 1 "2" 2003 1300
1 1 "2" 2004 1380
1 1 "2" 2005 1400
1 2 "1" 2000 2000
1 2 "1" 2001 2120
1 2 "1" 2002 2120
1 2 "1" 2003 2120
1 2 "1" 2004 2250
1 2 "1" 2005 2250
2 3 "4" 2000 1300
2 3 "4" 2001    0
2 4 "3" 2000 1500
2 4 "3" 2001 1600
2 4 "." 2002 1600
2 4 "." 2003 1800
2 4 "5" 2004 1800
2 5 "4" 2004 1400
end

I want to create a variable partner income (partnerinc) that shows the income of the partner in the corresponding year, if a partner was in the family of course.
The solution should look like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(family id) str1 partnerid int(year inc) str4 partnerinc
1 1 "2" 2000 1250 "2000"
1 1 "2" 2001 1250 "2120"
1 1 "2" 2002 1300 "2120"
1 1 "2" 2003 1300 "2120"
1 1 "2" 2004 1380 "2250"
1 1 "2" 2005 1400 "2250"
1 2 "1" 2000 2000 "1250"
1 2 "1" 2001 2120 "1250"
1 2 "1" 2002 2120 "1300"
1 2 "1" 2003 2120 "1300"
1 2 "1" 2004 2250 "1380"
1 2 "1" 2005 2250 "1400"
2 3 "4" 2000 1300 "1500"
2 3 "4" 2001    0 "1600"
2 4 "3" 2000 1500 "1300"
2 4 "3" 2001 1600 "0"  
2 4 "." 2002 1600 "."  
2 4 "." 2003 1800 "."  
2 4 "5" 2004 1800 "1400"
2 5 "4" 2004 1400 "1800"
end

I did it manually for these very few observation, but how do I create such a variable in general? I was looking through other posts on the forum but could not find a related one or get the hang of it when it was related.
I am using Stata 16.0. on Windows.

Thank you very much for your help in advance.

Best regards,
Guest

[Nick Cox]

A partner id that is string is at best awkward here. Also, you shouldn't want the new variable to be string.

For this data example, there is a trivial solution

bysort family year (id) : gen wanted = inc[3 - _n]

which yields inc[2] for observation 1 and inc[1] for observation 2.

Your real dataset is probably messier. rangestat from SSC is used here. Read its help for more guidance, or search the forum for mentions of rangestat.

clear
input byte(family id) str1 partnerid int(year inc)
1 1 "2" 2000 1250
1 1 "2" 2001 1250
1 1 "2" 2002 1300
1 1 "2" 2003 1300
1 1 "2" 2004 1380
1 1 "2" 2005 1400
1 2 "1" 2000 2000
1 2 "1" 2001 2120
1 2 "1" 2002 2120
1 2 "1" 2003 2120
1 2 "1" 2004 2250
1 2 "1" 2005 2250
2 3 "4" 2000 1300
2 3 "4" 2001    0
2 4 "3" 2000 1500
2 4 "3" 2001 1600
2 4 "." 2002 1600
2 4 "." 2003 1800
2 4 "5" 2004 1800
2 5 "4" 2004 1400
end

destring partnerid, replace 

rangestat wanted=inc if partnerid < ., interval(id partnerid partnerid) by(family year)

sort family year id 

list, sepby(family year)

    +-----------------------------------------------+
     | family   id   partne~d   year    inc   wanted |
     |-----------------------------------------------|
  1. |      1    1          2   2000   1250     2000 |
  2. |      1    2          1   2000   2000     1250 |
     |-----------------------------------------------|
  3. |      1    1          2   2001   1250     2120 |
  4. |      1    2          1   2001   2120     1250 |
     |-----------------------------------------------|
  5. |      1    1          2   2002   1300     2120 |
  6. |      1    2          1   2002   2120     1300 |
     |-----------------------------------------------|
  7. |      1    1          2   2003   1300     2120 |
  8. |      1    2          1   2003   2120     1300 |
     |-----------------------------------------------|
  9. |      1    1          2   2004   1380     2250 |
 10. |      1    2          1   2004   2250     1380 |
     |-----------------------------------------------|
 11. |      1    1          2   2005   1400     2250 |
 12. |      1    2          1   2005   2250     1400 |
     |-----------------------------------------------|
 13. |      2    3          4   2000   1300     1500 |
 14. |      2    4          3   2000   1500     1300 |
     |-----------------------------------------------|
 15. |      2    3          4   2001      0     1600 |
 16. |      2    4          3   2001   1600        0 |
     |-----------------------------------------------|
 17. |      2    4          .   2002   1600        . |
     |-----------------------------------------------|
 18. |      2    4          .   2003   1800        . |
     |-----------------------------------------------|
 19. |      2    4          5   2004   1800     1400 |
 20. |      2    5          4   2004   1400     1800 |
     +-----------------------------------------------+

[Guest]

Dear Nick,

thank you very much. It worked perfectly!