BookmarkSubscribeRSS Feed
Divya12
Calcite | Level 5

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')

5 REPLIES 5
ballardw
Super User

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
Calcite | Level 5

This is how the table will look like 

 

claimnumbercode
1010260
1010261
1010262
1010865
1020260
1020261
1020560
  
Output 
claimnumbercode
1010260
1010261
1010262
1010865

 

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
Amethyst | Level 16
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;
Thanks,
Jag
Divya12
Calcite | Level 5
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
Amethyst | Level 16

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;
Thanks,
Jag

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 619 views
  • 0 likes
  • 3 in conversation