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

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

buechler66 — Wed, 06 Apr 2016 20:36:33 GMT

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?

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

LinusH — Wed, 06 Apr 2016 20:53:43 GMT

Try the ANSI SQL Set operator EXCEPT.

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

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

buechler66 — Wed, 06 Apr 2016 23:04:42 GMT

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

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

ChrisBrooks — Wed, 06 Apr 2016 23:32:27 GMT

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;