topic Re: How to find the observation which has specific character and then remove it? in SAS Procedures

How to find the observation which has specific character and then remove it?

aminkarimid — Sun, 20 Aug 2017 22:45:22 GMT

Hello everybody;

I have a variable that contains alphanumeric strings of specific lengths, for example:

Name variable:
asdf1

asdg2

zxcv4

asdh3

qwer2

rtyu4

xcvb4

Now, I want to delete observations which have '4' in its name, for instance zxcv4. So, the result is:

Name variable:
asdf1

asdg2

asdh3

qwer2

And I need a list of deleted observation from dataset.

Here is the attributes of the TRD_STCK_CD variable in my dataset as the name variable.

Alphabetic List of Variables and Attributes

# Variable Type Len Format Informat Label

1 TRD_STCK_CD Char 15 $15. $15. TRD_STCK_CD

How can I do that?

Thanks in advance!

Re: How to find the observation which has specific character and then remove it?

Reeza — Sun, 20 Aug 2017 22:51:26 GMT

FIND() will find character, FINDW() will search for a word.

There are also INDEX and INDEXW() functions that operate similarily.

Rather than 'delete' you can control where an observation is output to using an OUTPUT statement.

Here's an example using SASHELP.CLASS

data females males;
set sashelp.class;

if sex='F' then output females;
else if sex='M' then output males;

run;

Re: How to find the observation which has specific character and then remove it?

aminkarimid — Sun, 20 Aug 2017 23:20:01 GMT

Hello @Reza;
I do not know why do you recommend 'output' statement.
I want to remove these data from my dataset in data cleaning procedure
related to my research.
I am using big data (more than 300 million).
Thanks.

Re: How to find the observation which has specific character and then remove it?

Reeza — Sun, 20 Aug 2017 23:23:37 GMT

Because you also said:

And I need a list of deleted observation from dataset.

Re: How to find the observation which has specific character and then remove it?

Shmuel — Sun, 20 Aug 2017 23:29:50 GMT

@aminkarimid, to remove an observation from a dataset, you either define which observation to delete or

define which to output.

So you can use either

data want; 
 set have;
      if index(variable,'4') > 0 then delete;
run;

or:

data want;
 set have;
      in index(variable,'4') = 0 then output;
run;

You will get same results in both cases.

You can use other functions than INDEX, like FIND - as @Reeza mentiond.

Re: How to find the observation which has specific character and then remove it?

Shmuel — Sun, 20 Aug 2017 23:32:33 GMT

You can also split your data into two datasets:

data ok nok;

set have;

if index(variable,'4') then output nok;

else output ok;

run;

Re: How to find the observation which has specific character and then remove it?

Jagadishkatam — Mon, 21 Aug 2017 02:07:28 GMT

I believe you already got some responses, just to add to those

if you want to remove the data please try the perl regular expression functions which are an alternative to the regular search functions.

data have;
input Name_variable $20.;
if prxmatch('m/4$/i',strip(Name_variable))=0;
cards;
asdf1
asdg2
zxcv4
asdh3
qwer2
rtyu4
xcvb4
;
run;