DATA Step, Macro, Functions and more

Conditional join on two variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 138
Accepted Solution

Conditional join on two variables

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!


Accepted Solutions
Solution
‎10-31-2014 09:29 AM
Super User
Super User
Posts: 7,401

Re: Conditional join on two variables

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;

View solution in original post


All Replies
Solution
‎10-31-2014 09:29 AM
Super User
Super User
Posts: 7,401

Re: Conditional join on two variables

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;

Super Contributor
Posts: 305

Re: Conditional join on two variables

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 730 views
  • 3 likes
  • 3 in conversation