How to pick records for ID's which dont have certain codes

[devsas]

Thanks for reading. So, I have a dataset as below-

data test;
input id $ claim_id $ rev_cd $;
datalines;
a a1 801
a a1 802
a a2 801
a a3 821
b b1 901
b b1 802
c c2 821
;
run;

I want to pick those Id's which do not have certain revenue code - for ex, 802- in any of its claims. Using Proc SQL as below it still picks a and b whereas I only want c.

proc sql;
select distinct id from test where rev_cd not in ( '802');
quit;

One way I can think of is having a table of records with id's having rev_cd as 802 and another table with id's not having rev_cd as 802. That way I can do a left join to extract C. But Im sure there is better way to solve this problem, but cant seem to think of it.

[FreelanceReinh · Accepted solution]

Hi @devsas,

To avoid subqueries or additional joins you can use GROUP BY and HAVING with a suitable summary function (e.g. MAX or SUM) operating on Boolean values:

proc sql;
select id
from test
group by id
having max(rev_cd='802')=0;
quit;

[koyelghosh]

I also could not think of a better way than you suggested. Ended up, typing your thought in a code form. Here it is.

PROC SQL;

	CREATE TABLE FirstTable AS 
	SELECT * 
	FROM test 
		WHERE rev_cd IN('802');

	SELECT Test.ID 
	FROM Test 
	LEFT JOIN FirstTable ON Test.ID=FirstTable.ID 
	WHERE Test.ID ^=FirstTable.ID;

QUIT;