Keep data only contains certain words but not anything else

Reply
New Contributor
Posts: 3

Keep data only contains certain words but not anything else

[ Edited ]

I would like to search a string variable only contains certain words but not anything else. 

 

The data would look like:

 

Osteoarthritis (OA)
OA (Osteoarthritis)
OA Knee
Osteoarthritis Knee
OA + hands
Osteoarthritis, hands
OA , hip
OA
Knee OA

So I would like to keep the rows containing OA/Osteoarthritis, or OA/Osteo with knee, but get rid of everything else. 

OA/Osteo with knee is easy to keep, but I am in difficulty to keep those only having word OA/Osteoarthritis. 

The variable is quite long and very messy, so impossible to list those I want to delete. 

Thanks

 

PROC Star
Posts: 7,492

Re: Keep data only contains certain words but not anything else

Given the example you posted, which records do you want to keep?

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 11,343

Re: Keep data only contains certain words but not anything else

When describing character searches you must be very explicit as character variables may contain much junk.

For instance you say you want to keep "rows containing OA/Osteoarthritis"

NONE of your example data shows OA/Osteoarthritis (the / character ). Do you mean the string values OA and Osteoarthritis appearing as words (meaning OA by itself not as part of Osteoarthritis or in the word oak)?

 

For OA/Osteo  similar with the added bit of Osteo not appearing in compound? But when you say with knee, since none of your example shows Osteo do you mean "OA" or "Osteo" and "Knee"?

Since your example data shows no instance of "Osteo" were you being lazy and not spelling "Osteoarthritis" completely? Spelling counts!

 

And will any of the words of interest be case sensitive: Find "Knee" but exclude "knee" (or vice versa)?

 

You know what you intend but computers are stupid and must be given very explicit instructions. Without those explicit rules we could make some guesses but might very well miss some conditions that seem obvious to you but we are not mind readers.

 

Here is a suggestion based on guesses.

assuming you want to match Knee or knee or kNee and similar

data want;
   set have;
   if (findw(str,'OA',' (),:;','i')>0 and findw(str,'OSTEOARTHRITIS',' (),:;','i' ))
      or 
      ( (findw(str,'OA',' (),:;','i') or findw(str,'OSTEOARTHRITIS',' (),:;','i') ) 
         AND findw(str,'KNEE',' (),:;','i') )
      
   ;
run;

where STR is the name of your variable with the text to search.

 

Ask a Question
Discussion stats
  • 2 replies
  • 110 views
  • 0 likes
  • 3 in conversation