topic Re: Keep data only contains certain words but not anything else in SAS Programming

Keep data only contains certain words but not anything else

windlove — Fri, 10 Mar 2017 04:42:26 GMT

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

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

art297 — Fri, 10 Mar 2017 14:11:26 GMT

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

Art, CEO, AnalystFinder.com

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

ballardw — Fri, 10 Mar 2017 17:22:57 GMT

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.