topic Re: Join/Merge tables but keeping unique match in SAS Programming

Join/Merge tables but keeping unique match

ysk — Tue, 18 Jan 2022 16:09:21 GMT

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?

Re: Join/Merge tables but keeping unique match

Reeza — Tue, 18 Jan 2022 16:14:25 GMT

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?

Re: Join/Merge tables but keeping unique match

s_lassen — Wed, 19 Jan 2022 08:50:42 GMT

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;

Re: Join/Merge tables but keeping unique match

Ksharp — Wed, 19 Jan 2022 12:54:39 GMT

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;