topic Re: left join on proc sql in SAS Programming

left join on proc sql

shubham1 — Mon, 08 Jan 2018 09:46:57 GMT

data new;

input x;

datalines;

;

run;

data new1;

input x;

datalines;

;

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;

Re: left join on proc sql

PeterClemmensen — Mon, 08 Jan 2018 09:55:31 GMT

perhaps like this

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

Re: left join on proc sql

Kurt_Bremser — Mon, 08 Jan 2018 10:15:00 GMT

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;

Re: left join on proc sql

RW9 — Mon, 08 Jan 2018 10:16:50 GMT

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;

Re: left join on proc sql

PaigeMiller — Mon, 08 Jan 2018 13:25:48 GMT

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