topic Re: Delete observations that CONTAIN in a variable a certain word in SAS Programming

Delete observations that CONTAIN in a variable a certain word

NKormanik — Thu, 10 Jan 2013 09:36:23 GMT

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?

Re: Delete observations that CONTAIN in a variable a certain word

RichardinOz — Thu, 10 Jan 2013 09:40:24 GMT

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

Re: Delete observations that CONTAIN in a variable a certain word

Fraktalnisse — Thu, 10 Jan 2013 10:02:04 GMT

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;

Re: Delete observations that CONTAIN in a variable a certain word

ghastly_kitten — Thu, 10 Jan 2013 10:04:44 GMT

data nicholas._21603_;

modify nicholas._21603_;

/* here are two variants */

/* first - if your task is only to search for a numbers */

if index(Combo,"23601") > 0 then remove;

/* if you need to pay attention to a character case, e.g. "asdASD" */

/* consider using find function */

/* if find(Combo, "asdASD", "i", 0) > 0 then remove; */

run;

Re: Delete observations that CONTAIN in a variable a certain word

ghastly_kitten — Thu, 10 Jan 2013 10:07:30 GMT

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.

Re: Delete observations that CONTAIN in a variable a certain word

NKormanik — Thu, 10 Jan 2013 22:36:58 GMT

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.

Re: Delete observations that CONTAIN in a variable a certain word

data_null__ — Thu, 10 Jan 2013 23:02:13 GMT

How about using the WHERE statment with CONTAINS.

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

Re: Delete observations that CONTAIN in a variable a certain word

Linlin — Fri, 11 Jan 2013 13:59:12 GMT

Hi DN,

What is the difference between delete and remove?

Thanks!

Re: Delete observations that CONTAIN in a variable a certain word

data_null__ — Fri, 11 Jan 2013 14:12:55 GMT

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.

Re: Delete observations that CONTAIN in a variable a certain word

Linlin — Fri, 11 Jan 2013 14:39:32 GMT

Thank you DN!

I think you meant

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

Re: Delete observations that CONTAIN in a variable a certain word

data_null__ — Fri, 11 Jan 2013 14:44:29 GMT

Re: Delete observations that CONTAIN in a variable a certain word

Linlin — Fri, 11 Jan 2013 14:50:01 GMT

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

Re: Delete observations that CONTAIN in a variable a certain word

data_null__ — Fri, 11 Jan 2013 14:58:07 GMT

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

Re: Delete observations that CONTAIN in a variable a certain word

Xiaoningdemao — Tue, 14 Jun 2016 19:52:04 GMT

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!

Re: Delete observations that CONTAIN in a variable a certain word

data_null__ — Tue, 14 Jun 2016 20:01:15 GMT

Start a new thread and give example data.

Re: Delete observations that CONTAIN in a variable a certain word

Xiaoningdemao — Tue, 14 Jun 2016 21:50:37 GMT

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

Re: Delete observations that CONTAIN in a variable a certain word

mgarrison — Mon, 11 Sep 2017 15:15:34 GMT

Thanks. This solution worked for me.