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

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 739 views
  • 3 likes
  • 5 in conversation