Generate new variable using a partner id

[Grace Rosario]

Hi Statalists,

I have a problem that doesn't seem to be quite the same as all the other examples that I can find around it. I hope that someone can help.

I have a dataset:
id wave age partnerid
001 1 50 002
001 2 51 002
001 3 52 002
002 1 36 001
002 2 37 001
002 3 38 001
003 2 19 .
003 3 20 005
003 4 21 005
004 2 70 .
005 3 18 003
005 4 19 003

I need to create a new variable "age_partner" which records the age of the partner in each line.
For example:

id wave age partnerid age_partner
001 1 50 002 36
001 2 51 002 37
001 3 52 002 38

I want to be able to repeat this for several variables including gender and income.
If anyone can help, I will be very grateful.
Many thanks in advance.

Grace

[Wouter Wakker]

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(id wave age partnerid)
1 1 50 2
1 2 51 2
1 3 52 2
2 1 36 1
2 2 37 1
2 3 38 1
3 2 19 .
3 3 20 5
3 4 21 5
4 2 70 .
5 3 18 3
5 4 19 3
end

preserve
keep if missing(partnerid)
tempfile nopartner
save `nopartner'

restore
drop if missing(partnerid)
tempfile a
save `a'

drop partnerid
rename (id age) (partnerid age_partner)

merge 1:1 partnerid wave using `a'
append using `nopartner'

order id wave age partnerid age_partner
sort id wave
list, noobs sepby(id) abbr(12)

. list, noobs sepby(id) abbr(12)

  +---------------------------------------------------------+
  | id   wave   age   partnerid   age_partner        _merge |
  |---------------------------------------------------------|
  |  1      1    50           2            36   matched (3) |
  |  1      2    51           2            37   matched (3) |
  |  1      3    52           2            38   matched (3) |
  |---------------------------------------------------------|
  |  2      1    36           1            50   matched (3) |
  |  2      2    37           1            51   matched (3) |
  |  2      3    38           1            52   matched (3) |
  |---------------------------------------------------------|
  |  3      2    19           .             .             . |
  |  3      3    20           5            18   matched (3) |
  |  3      4    21           5            19   matched (3) |
  |---------------------------------------------------------|
  |  4      2    70           .             .             . |
  |---------------------------------------------------------|
  |  5      3    18           3            20   matched (3) |
  |  5      4    19           3            21   matched (3) |
  +---------------------------------------------------------+

[Mike Lacy]

Here's a slightly different approach, which involves creating a temporary stand-in partnerid for those persons with a missing partnerid:

replace partnerid = -id if missing(partnerid)
//
preserve
tempfile temp
drop partnerid
rename (id age) (partnerid partnerage)
save `temp'
restore
//
merge 1:1 partnerid wave using `temp', keepusing(partnerage)
tab _merge
drop if (_merge == 2)
replace partnerid = . if (partnerid < 0) //

[Grace Rosario]

It works!
Thank you very much both of you for your help.

[Bhagya Gunawardena]

Dear all,

I have a somewhat similar question to Grace. How can I generate a variable which gives a common id to partners? For example (continuing Grace's example with similar data structure where one column has ids and another column has partner_id) - say a new variable 'couple' equal to 1 for both id 1 and 2, the variable couple equal to 2 for id 3 and 5 and likewise. In my dataset, all the subjects have a partner and only one partner (id is not repeated).
Any help is highly appreciated.