left join on proc sql

[shubham1]

data new;

input x;

datalines;

1

2

3

4

5

;

run;

data new1;

input x;

datalines;

3

4

5

;

run;

 I want my output to be 1 and 2 .I know I can acheive this through merge step but I want to use proc sql .I have used below proc sql 

code but when I am having large observation it takes too much time .Is their any other way i can use proc sql in this ?

proc sql;

create table final as

select a.x,b.x as y  from new as a

left join

new1 as b

on a.x=b.x

where b.x=.;

quit;

[PeterClemmensen]

perhaps like this

proc sql;
create table final as
select x from new 
where x not in
(select distinct x from new1);
quit;

[Kurt_Bremser]

When dealing with large tables, proc sort and data steps often outperform SQL. So use this:

data new;
input x;
datalines;
1
2
3
4
5
;
run;

data new1;
input x;
datalines;
3
4
5
;
run;

proc sort data=new;
by x;
run;

proc sort data=new1;
by x;
run;

data final;
merge
  new (in=a)
  new1 (in=b)
;
by x;
if a and not b;
run;

[RW9]

I agree with @Kurt_Bremser that datastep would be better suited for this:

data want;
  merge new (in=a) new1 (in=b);
  by x;
  if a and not b;
run;

But, you could use the except clause in SQL:

proc sql;
  create table WANT as
  select X
  from   NEW
  except 
  select X 
  from NEW1;
quit;

[PaigeMiller]

I never knew about the EXCEPT statement in PROC SQL. Here it is, a Monday morning, and I have learned something new.