topic "Not in" not working in New SAS User

"Not in" not working

SAS1918 — Wed, 07 Aug 2019 10:25:14 GMT

I have two variables patid ( patient ID) and dx1 (code).

For all patient id (patid) I want only those dx1 where dx1 start with with '380' or '470' OR dx1 is equal to '98006'

and replace any other dx1 with missing.

Please find below the SAS code -

data abc;
infile datalines ;
input patid dx1 $ ;
datalines;
123 38000
122 47001
123 38010
124 78011
125 47021
126 47031
127 38005
128 98006
;
run;

data abc1;
set abc;
if  upcase(substr(dx1,1,3)) not in ('380','470')  
or
 upcase(dx1) not in ('98006')  then dx1 = '';    
run;

The result is -

dx1 missing for all patid.

Why is that happening?

Re: "Not in" not working

Kurt_Bremser — Wed, 07 Aug 2019 10:35:56 GMT

The combined condition is true for all observations.

In all cases but the last, dx1 is not equal to '98006', so the second part of the condition is true, and in the last case, the first three digits are not in your list, so the first part of the condition is true. Did you intend to use "and" instead of "or"?

Re: "Not in" not working

Jagadishkatam — Wed, 07 Aug 2019 10:59:37 GMT

Please try the below code

data abc;
infile datalines ;
input patid dx1 $ ;
if not (prxmatch('m/^380/i',dx1) or prxmatch('m/^470/i',dx1) or prxmatch('m/^98006/i',dx1)) then dx1='';
datalines;
123 38000
122 47001
123 38010
124 78011
125 47021
126 47031
127 38005
128 98006
;
run;

Re: "Not in" not working

novinosrin — Wed, 07 Aug 2019 11:57:50 GMT

Hello @SAS1918 Are you sure that you are after "NOT IN"

You have stated " I want only those dx1 where dx1 start with with '380' or '470' OR dx1 is equal to '98006' "

Anyways


data abc;
infile datalines ;
input patid dx1 $ ;
datalines;
123 38000
122 47001
123 38010
124 78011
125 47021
126 47031
127 38005
128 98006
;
run;
/*if you want*/
data want;
set abc;
/*if you want*/
where dx1 in :('380','470') or dx1='98006' ;
run;

/*if you DO NOT want*/
data want;
set abc;
/*if you DO NOT want*/
where dx1 not in :('380','470') and dx1^='98006' ;
run;

Re: "Not in" not working

Ksharp — Wed, 07 Aug 2019 12:07:37 GMT

You are almost there.

data abc;
infile datalines ;
input patid dx1 $ ;
datalines;
123 38000
122 47001
123 38010
124 78011
125 47021
126 47031
127 38005
128 98006
;
run;

data abc1;
set abc;
if  dx1 not in: ('380','470','98006')  then dx1 = ' ';    
run;