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.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.