topic Re: IF statement that will delete a record if string contains "foo" in SAS Data Management

IF statement that will delete a record if string contains "foo"

jahanm — Tue, 26 Feb 2019 16:07:26 GMT

Hi,

Let's say my data set is set up like this:

study_id structure_id

1 BLADDER

2 .

3 BOWEL

4 BOWEL_SMALL

5 GTV

6 GTV_small

I want to delete records where structure_id contains "BOWEL" or "GTV".

I tried using this code:

data dvh_edited;
set dvh;
found=indexc(structure_id, "BOWEL", "GTV");
if found ^=0 then delete;
run;

But with that code it is looking for any letter in "BOWEL" or "GTV" so it's tagging many records. I'm not that familiar with PROC SQL so I'd prefer to do this in a data step if it's fairly simple. Thanks!

Re: IF statement that will delete a record if string contains "foo"

PaigeMiller — Tue, 26 Feb 2019 16:15:02 GMT

Use the FIND function

if find(structure_id, "BOWEL", "i") > 0 or find(structure_id,"GTV","i") > 0 then delete;

Re: IF statement that will delete a record if string contains "foo"

novinosrin — Tue, 26 Feb 2019 16:15:28 GMT


data have;
input study_id    structure_id  : $13.;
cards;
1                 BLADDER
2                 .
3                  BOWEL
4                  BOWEL_SMALL
5                  GTV
6                  GTV_small
;

data want;
set have;
found=findw(strip(structure_id), "BOWEL",'_') or findw(strip(structure_id), "GTV",'_');
if found ^=0 then delete;
run;

Re: IF statement that will delete a record if string contains "foo"

Astounding — Tue, 26 Feb 2019 16:21:26 GMT

It's easy if you are willing to code each word separately:

if findw(structure_id, 'GTV', , '_ ,' , 'I') then delete;

if findw(structure_id, 'BOWEL', , '_ ,' , 'I') then delete;

Assuming I got all the pieces of this in the right places:

The third parameter is starting position so can default to beginning of the string.

The fourth parameter is a list of word delimiters.

The fifth parameter asks that case be ignored, so these are both to be deleted: GTV and gtv

Note that this is probably more what you want, rather than INDEX or FIND. You probably don't want to delete observations that contain "HGTV", or "EMBOWEL".

Hmmm...... 3 responses in the first 10 minutes. Might be time for me to shut down the computer and go to lunch.

Re: IF statement that will delete a record if string contains "foo"

PaigeMiller — Tue, 26 Feb 2019 16:23:19 GMT

FINDW finds words, if the input text string is GTVsmall without the underscore, FINDW will not find it. I don't see a problem using FIND instead of FINDW.

Re: IF statement that will delete a record if string contains "foo"

Astounding — Tue, 26 Feb 2019 16:29:40 GMT

@PaigeMiller ,

I think the answer is to know something about your data. You can construct problem strings for most simple solutions (which includes mine, just for the record):

WatchingTV

Embowelling

Bowel/Intestine

Re: IF statement that will delete a record if string contains "foo"

PaigeMiller — Tue, 26 Feb 2019 16:33:19 GMT

Good point.

Re: IF statement that will delete a record if string contains "foo"

jahanm — Tue, 26 Feb 2019 21:35:47 GMT

Unfortunately the structure_id variable is human-entered freetext so I actually do run into the problem you stated. There are some variables "GTVp" that I also want to eliminate, and the findw code earlier doesn't seem to filter variants like that.

Re: IF statement that will delete a record if string contains "foo"

Patrick — Wed, 27 Feb 2019 23:30:15 GMT

@jahanm wrote:

Unfortunately the structure_id variable is human-entered freetext so I actually do run into the problem you stated. There are some variables "GTVp" that I also want to eliminate, and the findw code earlier doesn't seem to filter variants like that.

Regular expressions allow you to search for text patterns - you just need to be able to formulate the rules for these patterns.

Below RegEx will match any string with bowel or gtv in it. But using a RegEx would also allow you to search for a pattern like a word consisting of GTV plus max. one additional letter.

data have;
  input study_id structure_id : $13.;
  cards;
1 BLADDER
2 .
3 BOWEL
4 BOWEL_SMALL
5 GTV
6 GTV_small
;

data want;
  set have;
  if prxmatch('/bowel|gtv/oi',structure_id)>0 then delete;
run;