Delete letters/numbers from the observation in a particular variable

[Dipu]

Hello,

Can please guide me-

I have the dataset with 20 variables and thousands of records-

One of the variable name-

VAR1 contains--

a

e

f

ased

f

d

egf

dgh

cedh

.

.

continued 1000's of records

I would like to delete the records containing any of these three letters 'e', 'f', 'd'.

When I used this- (as shown below), I could delete only records with only  'e', 'f', 'd', I want to all the records containing these three letters- they may individual or in a group.

data DM2;

set   DM1;

if VAR1 in ( 'E', 'F', 'D') then delete;

run;

Thank you

[Reeza]

Look at the index and find functions as a starting point.

Right now you're doing an exact comparison, but you want to search the string really.

[Dipu]

Hi Reeza,

Thank you for the reply but I did not get you, what exactly you mean.

As such I could not understand the comparison and I am trying to delete those records containing these string 'e', 'f'', 'd'.

Thanks

[pflickner]

if index(lowcase(var1), "e") or index(lowcase(var1), "f") or index(lowcase(var1), "d") then delete;

I'm sure there's a shorter way, but this is all I could think of on the spot.

[Reeza · Accepted solution]

Use the FINDC function.

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

findc(var1, "efd", 'it')

var1 - your variable to check

"efd" the characters you're checking for

'it' - modifiers, tells SAS to ignore case when checking (Characters are case sensitive, ie E does not equal e) and to trim trailing blanks.

The first example data set shows you what the findc function returns, and the Want data set shows you how to keep only obs of interest.

data have;

input var1 $;

cards;

a

e

f

ased

f

d

egf

dgh

cedh

jag

hat

sat

car

;

run;

data example;

set have;

var2=findc(var1, "efd", 'it');

run;

data want;

    set have;

    if findc(var1, "efd", 'it')=0;

run;

[pflickner]

You're a treasure!

[Haikuo]

Of course Reeza is more official, this is what those functions(findc, find, index, PRXs) made for. However, your question prompted me a unconventional usage of another function: compress():

data have;

input var1 $;

cards;

a

e

f

ased

f

d

egf

dgh

cedh

jag

hat

sat

car

;

run;

data want;

  set have;

    if lengthn(var1) = lengthn(compress(var1,'efd','i'));

run;

Please note, even this works for you, I would still recommend  Reeza's solution, which involves less functions and supposedly more efficient. My solution is FWIW only.

Haikuo

[Reeza]

I also found another function, VERIFY, that I hadn't seen before. I'll have to test it first before suggesting it though

[Dipu]

Thank you Rizza, Pflickner and Hai.Kuo.

Than you So much.

Dipu

[RichardinOz]

An easier way to use compress, with the K (=keep) parameter:

data want;

  set have;

    if missing (compress(var1,'efd','K'));

run;

Not tested

Richard