Delete observations that CONTAIN in a variable a certain word

[NKormanik]

The following works great:

data nicholas._21603_;

modify nicholas._21603_;

if Combo="23601" then remove;

run;

But what I want to now do is change the "=" sign to "contains":

if Combo contains "23601" then remove;

ERROR 388-185: Expecting an arithmetic operator.

ERROR 76-322: Syntax error, statement will be ignored.

Great luck, huh.

So..., what gives?

[RichardinOz]

Contains is available only in Proc SQL and is there for compatibility.  There are other functions such as variants of the Index function that can be used in a data step to achieve the same result.

Richard

[Fraktalnisse · Accepted solution]

Hi!

The Contains operator is not limited to Proc Sql. It can be used in the data step, but only in a Where statement.

I suggest the following:

data nicholas._21603_;

modify nicholas._21603_;

if find(Combo,"23601") then remove;

run;

[ghastly_kitten]

Find function is a bit slower than index at least in some SAS versions.

I'm not sure about 9.3, but checking for modifiers and startposition spends some time, especially on huge datasets.

Anyway, better check out the help for additional info, who knows what you will need it for.

[NKormanik]

Sweet!!  Thanks a million.

data nicholas._21603_;

modify nicholas._21603_;

if find(Combo,"23601") then remove;

if find(Combo,"23602") then remove;

if find(Combo,"23603") then remove;

run;

NOTE: There were 78939 observations read from the data set NICHOLAS._21603_.

NOTE: The data set NICHOLAS._21603_ has been updated.  There were 0 observations rewritten, 0 observations added and 5733 observations deleted.

[data_null__]

How about using the WHERE statment with CONTAINS.

data class;
   set sashelp.class;
   run;
data class;
   modify class;
   where name contains 'il';
   remove;
   run;

[Linlin]

Hi DN,

What is the difference between delete and remove?

Thanks!

[data_null__]

DELETE is RETURN without implied OUTPUT

REMOVE is for deleting records with MODIFY statement.

DELETE and RETURN are the same in a data step with explicit OUTPUT.

[Linlin]

Thank you DN!

I think you meant

DELETE and REMOVE are the same in a data step with explicit OUTPUT.

[data_null__]

NO

[Linlin]

Thank you! I have never used REMOVE. I need to read more about it. - Linlin

[data_null__]

Until you have used MODIFY you don't need REMOVE.

[Xiaoningdemao]

Hi data_null_,
I have a similar problem, but I want to remove observations with sting 'aaa' and 'bbb' at thre same time, how to do that? Can I write two where at the same time?
Thanks in advance!

[data_null__]

Start a new thread and give example data.

[Xiaoningdemao]

Hi data_null__,
I post the question named as "Delete obsvations with certain strings".
Thanks a lot!