proc sql : where condition with multiple varibles

AlexeyS — Thu, 02 Aug 2018 06:51:54 GMT

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?

Re: proc sql : where condition with multiple varibles

Kurt_Bremser — Thu, 02 Aug 2018 07:18:46 GMT

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;

topic proc sql : where condition with multiple varibles in SAS Procedures

proc sql : where condition with multiple varibles

Re: proc sql : where condition with multiple varibles