Matching observations in the same table

Question

I was wondering if SAS can match observations in the same table based on multiple matching conditions and then tag them. For instance I want observations which matches on age and gender but only differs based on if they like star wars or not.

This is the data I have

   age  gender  Like star wars  Location
    34  male         1            US
    36  female       0            UK
    24  female       1            AU
    45  female       1            US
    34  male         0            CH
    36  female       1            US
    57  female       0            US

Want

>    age    gender  Like star wars  Location    Match
>     34    male         1            US         Yes
>     36    female       0            UK         Yes
>     24    female       1            AU         No
>     45    female       1            US         No
>     34    male         0            CH         Yes
>     36    female       1            US         Yes
>     57    female       0            US         No

What have you tried ? Can you explain the rules for Match, particularly the cases of 34-male and 36-female. — Richard
I am struggling with PROC SQL, group by age and gender, but don't know how to assign a tag for the results. The match for 34-male is made since they have the same age and gender, but differ on liking to star wars, irrespective of their location — Kul
So is the match because one likes and one does not like ? If both liked or both disliked would the match be 'No' ? What it there were three observations with 34-male -- how would Match be assigned based on the 'Like star wars' ? — Richard

floydn floydn · Accepted Answer · 2018-04-25T05:04:33

If you sort the data you have by age and gender, then the "matches" will be grouped together. The non-matches can easily be detected with first. and last. processing. Rows that are both first. and last. do not have a match.

proc sort data=have;
by age gender;

data want;
set have;
by age gender;
match = ifc(first.age and last.age, 'No', 'Yes');
run;

Matching observations in the same table

1 Answers