topic Conditional join on two variables in SAS Programming

Conditional join on two variables

Walternate — Fri, 31 Oct 2014 13:15:03 GMT

Hi,

I have two datasets I am trying to join. One of them has ID, and the other one has ID1 and ID2. ID2 is only populated if someone's ID1 changes (the old value goes in ID2, while the new value goes in ID1). What I want to do is join the datasets such that as many people join as possible. That means I want to join ID to ID1, but then, for those where there is no ID1 match, I'd like to join ID to ID2. I know how to use a proc sql statement to join on two variables, but I don't know of a way to add conditions to the join.

Any help is much appreciated.

Thanks!

Re: Conditional join on two variables

RW9 — Fri, 31 Oct 2014 13:29:31 GMT

Hi,

proc sql;

create table WANT as

select A.*,

B.*

from TABLEA A

full join TABLEB B

on A.ID=COALESCE(B.ID2,B.ID1);

quit;

/* I.e. if ID2 exists then use that for compare, else use ID1 */

You could also do the join a couple of times:

proc sql;

...

on A.ID=B.ID1

full join TABLEB

on A.ID=B.ID2;

quit;

Re: Conditional join on two variables

Loko — Fri, 31 Oct 2014 14:21:40 GMT

Hello,

/*prepare some data*/

data a;
set sashelp.class;
if mod(_N_,2)=1 then do name1=name;age=0;end;
else do;
name1="ABC";
name2=name;
age=15;
end;

if _n_=19 then delete;
run;

proc sql;
create table want as
select b.name, a.age
from a,sashelp.class b
where
b.name=a.name1 or b.name=a.name2;
quit;