Hi everyone,
I have a dataset that contains respondents' cognitive functioning and whether they receive care from their spouses. As shown in the below sample data, the data contains both respondent's ID (RespondentID) and their spouse's ID (SpouseID) as well as their household ID (HouseholdID).

As shown in my goal dataex, I am trying to generate a new caregiver variable (e.g., caregiver_new) that links respondents' cognitive functioning and their spouses' caregiving status. For example, row #1 and #2 (RespondentID=20) provided care for his/her spouse (RespondentID=30) that as shown in row #4 and #5 has a normal cognitive functioning. As such, this person is assigned normal caregiver. Row #3 and #4 is non-caregiver and therefore caregiver_new become simply non-caregiver. As another example, row #5 and #6 provided care for his/her spouses who is demented as such the person is assigned "demented caregiver." Thanks.
Nader



*my current data
clear
input str20(HouseholdID RespondentID SpouseID cogfunction CareRecipient Caregiver)
010059 020 030 Normal No caregiver
010059 020 030 Normal No caregiver
010059 030 020 Normal Yes Non_caregiver
010059 030 020 Normal Yes Non_caregiver
010075 020 030 Normal No caregiver
010075 020 030 CIND No caregiver
010075 030 020 Dmented Yes Non_caregiver
010075 030 020 Dmented Yes Non_caregiver
end


*my goal
clear
input str20(HouseholdID RespondentID SpouseID cogfunction CareRecipient Caregiver caregiver_new)
010059 020 030 Normal No caregiver Normal caregiver
010059 020 030 Normal No caregiver Normal caregiver
010059 030 020 Normal Yes Non_caregiver Non_caregiver
010059 030 020 Normal Yes Non_caregiver Non_caregiver
010075 020 030 Normal No caregiver Dmented caregiver
010075 020 030 CIND No caregiver Dmented caregiver
010075 030 020 Dmented Yes Non_caregiver Non_caregiver
010075 030 020 Dmented Yes Non_caregiver Non_caregiver

end