DATA Step, Macro, Functions and more

NOT IN statement in DO loop?

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

NOT IN statement in DO loop?

Hello!

 

I want to exclude rows which has "45981" string in any of the 4 columns DX1-DX4. I used DO loop but it's not doing anything and neither throws error.

 

DATA E.Val1;
SET E.Comb_1234;
ARRAY ICD {4} DX1-DX4;
	DO i = 1 to 4;
			IF ICD {i} not in: ("45981") THEN DO; 
		OUTPUT; 
		LEAVE; 
		END;
	END;
RUN;

Can I use "NOT IN" in the above code?

 

Thanks!


Accepted Solutions
Solution
‎07-12-2017 09:54 AM
Super User
Posts: 19,770

Re: NOT IN statement in DO loop?

Posted in reply to Sujithpeta

They are different comparisons. 

 

Try one of the follow methods instead:

 

DATA E.Val1;
SET E.Comb_1234;
ARRAY ICD {4} DX1-DX4;

if whichc('45981', of icd(*))>0 then delete;

RUN;

or:

 

DATA E.Val1;
SET E.Comb_1234;
ARRAY ICD {4} DX1-DX4;
flag=0;
	DO i = 1 to 4;
			IF ICD {i} in  ("45981") THEN  flag=1; 
		         if flag=1 then  LEAVE; 
		END;
	if flag=1 then delete;
RUN;

 

 

View solution in original post


All Replies
Super User
Posts: 19,770

Re: NOT IN statement in DO loop?

Posted in reply to Sujithpeta

You're using the IN: which checks the beginning of the string matches. Is that what you intended to do? Otherwise I would recommend using WHICHC.

Contributor
Posts: 43

Re: NOT IN statement in DO loop?

Hi, I also tried using "NOT IN" instead "NOT IN:" it doesn't work.

PROC Star
Posts: 1,759

Re: NOT IN statement in DO loop?

[ Edited ]
Posted in reply to Sujithpeta

Works fine for me

data TEST;
  set SASHELP.CLASS(obs=3);
  array ICD {2} NAME SEX;
  do I = 1 to 2;
    putlog _N_= I= NAME= ;
    if ICD {I} not in: ("Ali") then leave; 
  end;
run;

 

 

_N_=1 i=1 Name=Alfred
_N_=2 i=1 Name=Alice
_N_=2 i=2 Name=Alice
_N_=3 i=1 Name=Barbara

 

Solution
‎07-12-2017 09:54 AM
Super User
Posts: 19,770

Re: NOT IN statement in DO loop?

Posted in reply to Sujithpeta

They are different comparisons. 

 

Try one of the follow methods instead:

 

DATA E.Val1;
SET E.Comb_1234;
ARRAY ICD {4} DX1-DX4;

if whichc('45981', of icd(*))>0 then delete;

RUN;

or:

 

DATA E.Val1;
SET E.Comb_1234;
ARRAY ICD {4} DX1-DX4;
flag=0;
	DO i = 1 to 4;
			IF ICD {i} in  ("45981") THEN  flag=1; 
		         if flag=1 then  LEAVE; 
		END;
	if flag=1 then delete;
RUN;

 

 

Super User
Posts: 5,498

Re: NOT IN statement in DO loop?

Posted in reply to Sujithpeta

Logically, your loop does the wrong thing.  If it finds the right match, it still looks through the remaining codes and outputs the observation.  So, yes, essentially it does nothing because it will output every observation unless all four of them are "45981".  Reeza has given you some ways around this, or you could use your own program and replace this part:

 


			IF ICD {i} not in: ("45981") THEN DO; 
		OUTPUT; 
		LEAVE; 

 

Change it to:

 

if icd{i} =: "45981" then delete; 

Regular Contributor
Posts: 191

Re: NOT IN statement in DO loop?

Posted in reply to Sujithpeta

Something like this?

 

data test;

length dx1-dx4 $32;

input dx1-dx4;

datalines;

a45981 b c d

a b45981 c d

a b c45981 d

a b c d45981

a b c d

;

run;

 

data test2;

set test;

if prxmatch( '/.*45981.*/', cats(OF dx1-dx4)) then delete;

run;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 182 views
  • 9 likes
  • 5 in conversation