Claim can have certain diagnosis code but not a particular code

[Divya12]

Hi,

I need to select claims which have certain diagnosis code but should not a have a particular code within the submitted claim. 

Condition - 

A claim can have Codes in ('0260','0261','0262','0263')

but should not have code ('560')

[ballardw]

You might want to provide some example of what the data looks like and what you actually expect the output to look like.

For instance, we cannot tell if you are adding one or more variables or keeping/removing records from a data set.

Does each record have exactly one code variable to compare or are there multiple codes?

Some coding systems with diagnosis may also have a decimal portion such as 0260.1 0260.2 or similar. If your values look like this then are you comparing the whole value or just the base part?

[Divya12]

This is how the table will look like 

claimnumber	code
101	0260
101	0261
101	0262
101	0865
102	0260
102	0261
102	0560
Output
claimnumber	code
101	0260
101	0261
101	0262
101	0865

Here we will not be selecting claim 102 because it has '0560' code in it but we will select 101 as it has codes which are  in  ('0260','0261','0262','0263')

[Jagadishkatam]

data have;
input claimnumber code;
cards;
101 0260
101 0261
101 0262
101 0865
102 0260
102 0261
102 0560
;

proc sql;
create table want as select * from have where claimnumber not in (select claimnumber from have where code=0560);
quit;

[Divya12]

But i would also have to check if they have codes in ('0260','0261','0262','0263') because not every claim may have these codes.

[Jagadishkatam]

you may try the below code

proc sql;
create table want as select * from have where claimnumber not in (select claimnumber from have where code=0560) and claimnumber in (select claimnumber from have where code in (0260,0261,0262,0263));
quit;