Delete observations that CONTAIN in a variable a certain word

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

Delete observations that CONTAIN in a variable a certain word

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?


Accepted Solutions
Solution
‎01-10-2013 05:02 AM
SAS Employee
Posts: 23

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

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;

View solution in original post


All Replies
Super Contributor
Posts: 644

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

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

Solution
‎01-10-2013 05:02 AM
SAS Employee
Posts: 23

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

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;

Contributor
Posts: 41

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

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.

Regular Contributor
Posts: 212

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

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.

Respected Advisor
Posts: 3,776

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

How about using the WHERE statment with CONTAINS.

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

Super Contributor
Posts: 1,636

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

Hi DN,

What is the difference between delete and remove?

Thanks!

Respected Advisor
Posts: 3,776

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

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.

Super Contributor
Posts: 1,636

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

Thank you DN!

I think you meant

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

Respected Advisor
Posts: 3,776

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

NO

Super Contributor
Posts: 1,636

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

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

Respected Advisor
Posts: 3,776

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

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

Contributor
Posts: 73

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

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!
Respected Advisor
Posts: 3,776

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

Start a new thread and give example data.

Contributor
Posts: 73

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

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 5172 views
  • 7 likes
  • 7 in conversation