BookmarkSubscribeRSS Feed
shubham1
Calcite | Level 5

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;

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26

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

--
Paige Miller

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 684 views
  • 3 likes
  • 5 in conversation