DATA Step, Macro, Functions and more

left join on proc sql

Reply
User
Posts: 1

left join on proc sql

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;

PROC Star
Posts: 1,190

Re: left join on proc sql

perhaps like this

 

proc sql;
create table final as
select x from new 
where x not in
(select distinct x from new1);
quit;
Super User
Posts: 9,560

Re: left join on proc sql

[ Edited ]

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,200

Re: left join on proc sql

I agree with @KurtBremser 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;
Respected Advisor
Posts: 2,647

Re: left join on proc sql

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

--
Paige Miller
Ask a Question
Discussion stats
  • 4 replies
  • 101 views
  • 3 likes
  • 5 in conversation