Join/Merge tables but keeping unique match

[ysk]

Hi there,

I am trying to merge two data tables (X and Y) by linking on multiple variables (e.g., age and gender to find matching cases)

Table X 

Participant ID Age Gender

Table Y

Encounter# Age Gender

I did a left join and output shows that some participant IDs are matched up with same encounter #s since the age and gender are equivalent. 

I'm hoping to retain unique encounter #s so if an encounter# is matched with participant ID it will not show up again with other participant ID.

How could I do this? 

[Reeza]

You can't with SQL but you can with other approaches. Greedy matching?

Mayo Clinic has a macro:

https://bioinformaticstools.mayo.edu/research/gmatch/

And there's PROC PSMATCH for propensity score matching.

---

@ysk wrote:

Hi there,

I am trying to merge two data tables (X and Y) by linking on multiple variables (e.g., age and gender to find matching cases)

Table X 

Participant ID Age Gender

Table Y

Encounter# Age Gender

I did a left join and output shows that some participant IDs are matched up with same encounter #s since the age and gender are equivalent. 

I'm hoping to retain unique encounter #s so if an encounter# is matched with participant ID it will not show up again with other participant ID.

How could I do this? 

---

[s_lassen]

I think it can be done with SQL, something like this:

  select 
    x.*,uniq.encounter from
    x left join (
      select x.*,y.encounter
      from x join y on x.age=y.age and x.sex=y.sex
      group by y.encounter
      having x.participant_id=min(x.participant_id)
   ) uniq on x.participant_id=uniq.participant_id;

[Ksharp]

You want this ?

data x;
input id age sex;
cards;
1 19 1
1 20 0
1 40 0
2 19 1
2 20 0
;

data y;
input id age1 sex1;
cards;
1 19 1
1 20 0
2 19 1
2 20 0
2 22 1
;

data want;
 merge x y;
 by id;
 output;
 call missing(of _all_);
run;