DATA Step, Macro, Functions and more

Selecting only records from Dataset A where the same records do not exist in Dataset B

Reply
Regular Contributor
Posts: 212

Selecting only records from Dataset A where the same records do not exist in Dataset B

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? 

Super User
Posts: 5,260

Re: Selecting only records from Dataset A where the same records do not exist in Dataset B

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
Regular Contributor
Posts: 212

Re: Selecting only records from Dataset A where the same records do not exist in Dataset B

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

Regular Contributor
Posts: 190

Re: Selecting only records from Dataset A where the same records do not exist in Dataset B

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;
Ask a Question
Discussion stats
  • 3 replies
  • 211 views
  • 0 likes
  • 3 in conversation