BookmarkSubscribeRSS Feed
buechler66
Barite | Level 11

I have Dataset A and Dataset B both with the variables Imb_code and Rule_Number. I need to keep only records in Dataset A that do not exist in Dataset B.  In Oracle SQL I would use a Minus operator, something like this...

 

select imb_code, rule_number

from TableA

Minus

select imb_code, rule_number

from TableA;

 

How would this best be done in SAS? 

3 REPLIES 3
LinusH
Tourmaline | Level 20

Try the ANSI SQL Set operator EXCEPT.

Another option would be to left join and filter out matches (test on IMB_CODE IS NULL).

Data never sleeps
buechler66
Barite | Level 11

Really struggling here with syntax.  Any chance someone can throw our an example of these two suggestions?

ChrisBrooks
Ammonite | Level 13

As Linus suggests EXCEPT is the solution here e.g.

 

data tablea;
	infile datalines dlm=",";
	input imb_code rule_number;
datalines;
1,9
2,8
3,7
4,6
5,5
;
run;

data tableb;
	infile datalines dlm=",";
	input imb_code rule_number;
datalines;
1,9
4,6
;
run;

proc sql;
	create table tablec as
	select * from tablea
except
	select * from tableb
	;
quit;

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
  • 3 replies
  • 815 views
  • 0 likes
  • 3 in conversation