Conditional join on two variables

[Walternate]

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!

[RW9 · Accepted solution]

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;

[Loko]

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;