BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sujithpeta
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Reeza
Super User

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.

Sujithpeta
Quartz | Level 8

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

ChrisNZ
Tourmaline | Level 20

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

 

Reeza
Super User

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;

 

 

Astounding
PROC Star

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; 

FredrikE
Rhodochrosite | Level 12

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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