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;
perhaps like this
proc sql;
create table final as
select x from new
where x not in
(select distinct x from new1);
quit;
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;
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;
I never knew about the EXCEPT statement in PROC SQL. Here it is, a Monday morning, and I have learned something new.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.