topic Re: Merge if any of a set of variables matches in SAS Programming

Merge if any of a set of variables matches

jjb123 — Mon, 25 Mar 2019 18:30:09 GMT

I want to merge (using left join) using the id variable in each set and based on var1, var2, var3...etc. (var1-36). However, I don't want to require that they all match. For instance, I want the information to merge whether the only match is a.var2 and b.var7 or if ten match or if they all match. Furthermore, there are missing values for some variables for almost all observations, so I want to ensure that missing matches are not captured. Any help is appreciated.

proc sql;
create table want as
select 		a.*, b.info
from 		have1 as a left join have2 as b
on			a.id = b.id and
 (a.var1-36 = b.var1-36)
order by           id, date;
quit;

Re: Merge if any of a set of variables matches

Astounding — Mon, 25 Mar 2019 18:48:40 GMT

Are you saying you want a match of the IDs match and a.var1 is equal to any of the fields b.var1 through b.var36 ?

Re: Merge if any of a set of variables matches

jjb123 — Mon, 25 Mar 2019 19:11:05 GMT

Sort of. I want a match if the IDs match and any of a.var1, a.var2, a.var3 (i.e., a.var1-36) is equal to any of b.var1, b.var2, b.var3 (i.e., b.var1-36).

Re: Merge if any of a set of variables matches

ballardw — Mon, 25 Mar 2019 20:21:21 GMT

Example input data from both sets and the result needed.

Re: Merge if any of a set of variables matches

jjb123 — Mon, 25 Mar 2019 22:45:10 GMT

Assume the first data set is the have dataset (it can be have1 and have2 for simplicity). Assume the second is the desired result. The variables I used are a little different, so I'll update my original code as well.

proc sql;
create table want as
select 		a.*, b.info as merged_info
from 		have as a left join have as b
on			a.unique ne b.unique and a.id = b.id and
 (a.var1-4 = b.var1-4)
order by           unique, id;
quit;

Re: Merge if any of a set of variables matches

mkeintz — Mon, 25 Mar 2019 22:47:39 GMT

@jjb123 wrote

.... Furthermore, there are missing values for some variables for almost all observations, so I want to ensure that missing matches are not captured. Any help is appreciated.

So what do you want to do if the variable is missing in one data set and not missing in the other? Does that constitute a non-match?

Re: Merge if any of a set of variables matches

jjb123 — Mon, 25 Mar 2019 23:23:38 GMT

I'm not sure I understand your question. A missing value (which would only be a match if it matches to another missing value) should always be considered a non-match.

Re: Merge if any of a set of variables matches

andreas_lds — Tue, 26 Mar 2019 01:52:00 GMT

If you want (tested) code provide data in usable form.

Re: Merge if any of a set of variables matches

jjb123 — Tue, 26 Mar 2019 03:32:40 GMT

Here you go.

data have;
input unique id var1 var2 var3 var4 info;
datalines;
1 1 . . . 1234 54
2 1 32423 3713 1234 328931 26
3 1 3713 1234 123412 3253 82
4 2 4567 . . 12 93
5 2 . . . 1267 102
6 2 12 145 86 92 96
7 3 . 8214 1479 . 123
8 3 . . . . 85
9 3 987 345 7528 93842 146
;

Re: Merge if any of a set of variables matches

andreas_lds — Tue, 26 Mar 2019 06:47:04 GMT

Thanks, but again incomplete. In your sql code three datasets are mentioned: a, b and want. Which of the three is "have"?

Re: Merge if any of a set of variables matches

Patrick — Tue, 26 Mar 2019 07:10:33 GMT

@jjb123

If using SQL with SAS tables then here one way to go.

data have;
  input unique id var1 var2 var3 var4 info;
datalines;
1 1 . . . 1234 54
2 1 32423 3713 1234 328931 26
3 1 3713 1234 123412 3253 82
4 2 4567 . . 12 93
5 2 . . . 1267 102
6 2 12 145 86 92 96
7 3 . 8214 1479 . 123
8 3 . . . . 85
9 3 987 345 7528 93842 146
;
run;

proc sql;
  create table want as
    select  a.*, b.info as merged_info
      from  have as a left join have as b
        on  a.unique ne b.unique and a.id = b.id and
            (
              whichn(a.var1,.,b.var1,b.var2,b.var3,b.var4) >1 or
              whichn(a.var2,.,b.var1,b.var2,b.var3,b.var4) >1 or
              whichn(a.var3,.,b.var1,b.var2,b.var3,b.var4) >1 or
              whichn(a.var4,.,b.var1,b.var2,b.var3,b.var4) >1
            )
      order by unique, id
      ;
quit;

Re: Merge if any of a set of variables matches

jjb123 — Tue, 26 Mar 2019 21:11:09 GMT

This works like a charm. Thank you.