proc sql : where condition with multiple varibles

[AlexeyS]

I want to keep all observation(after merging  tb1 and tbl2 ), but drop from it all observations that exist in tbl3.

the code is used is as follows:

proc sql;
create table tbl4 vas
select t1.*
from tbl1 t1
right join tbl2 t2 on t1.x1=t2.x1 and t1.x2=t2.x2 and t1.x3=t2.x3
where t1.x1 not in(select x1 from tbl3) and t1.x2 in(select x2 from tbl3) and t1.x3 in(select x3 from tbl3)
order by x1, x2, x3
;
quit;

What is wrong?

[Kurt_Bremser · Accepted solution]

Just an idea:

proc sql;
create table tbl4 as
  select t1.*
  from tbl1 t1
  right join tbl2 t2 on t1.x1=t2.x1 and t1.x2=t2.x2 and t1.x3=t2.x3
  left join tbl3 t3 on t1.x1=t3.x1 and t1.x2=t3.x2 and t1.x3=t3.x3
where t3.x1 is missing
order by t1.x1, t1.x2, t1.x3
;
quit;