Replace value in variable with value in another variable at a systematically different location

[Aaradhya Gupta]

I have data of the following basic structure: In each household (indicated by hid), there are multiple individuals (indicated by id). Every household has a unique reference person, marked as 1 in ra0100. Similarly, the spouse of the reference person is indicated by ra0100== 2, and so on. Finally, pe0100a shows the labour status of each household member.

What I want to do is to create a new variable that will contain only the employment status of the spouse. Usually, I would do that by:

bys hid: gen try = pe0100a if ra0100 == 2

However, later in the data processing, I will be doing: keep if ra0100==1, and hence want the spouse's labour status in the same corresponding line as the reference person's employment status.

In that way, when I do the keep if ra0100==1, pe0100a will tell the reference person's labour status, and this new variable will show the spouse's labour status. Is this possible to do? I do not want to reshape my data, as the structure of the data makes it quite tedious.

* Example generated by -dataex-. For more info, type help dataex
clear
input str12(id hid) byte(ra0100 pe0100a)
"AT1100102012" "AT1100102002" 1 1
"AT1100102022" "AT1100102002" 3 1
"AT1100104012" "AT1100104002" 1 5
"AT1100106012" "AT1100106002" 1 5
"AT1100201012" "AT1100201002" 1 5
"AT1100203012" "AT1100203002" 1 5
"AT1100205012" "AT1100205002" 1 5
"AT1100208012" "AT1100208002" 1 5
"AT1100301012" "AT1100301002" 1 1
"AT1100301022" "AT1100301002" 2 1
"AT1100305012" "AT1100305002" 1 5
"AT1100501012" "AT1100501002" 1 5
"AT1100503012" "AT1100503002" 1 1
"AT1100503022" "AT1100503002" 2 1
"AT1100503032" "AT1100503002" 3 .
"AT1100504012" "AT1100504002" 1 3
"AT1100506012" "AT1100506002" 1 1
"AT1100506022" "AT1100506002" 2 8
"AT1100506032" "AT1100506002" 3 .
"AT1100601012" "AT1100601002" 1 4
end
label var id "ID" 
label var hid "Household ID" 
label var ra0100 "RA0100 relationship to reference person" 
label var pe0100a "PE0100a labour status a - MAIN labour status"

A small point is that some houses do not have a 'spouse', so employment status should be set to missing for these.

Thank you,
Aaradhya

[Øyvind Snilsberg]

bys hid (ra0100): gen wanted = cond(ra0100[2]==2,pe0100a[2],.)

[Aaradhya Gupta]

Wow, that works perfectly. Thank you so much!