Delete letters/numbers from the observation in a particular variable

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

Delete letters/numbers from the observation in a particular variable

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


Accepted Solutions
Solution
‎01-30-2014 12:47 PM
Super User
Posts: 19,815

Re: Delete letters/numbers from the observation in a particular variable

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;

View solution in original post


All Replies
Super User
Posts: 19,815

Re: Delete letters/numbers from the observation in a particular variable

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.

Contributor
Posts: 43

Re: Delete letters/numbers from the observation in a particular variable

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

Occasional Contributor
Posts: 10

Re: Delete letters/numbers from the observation in a particular variable

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.

Solution
‎01-30-2014 12:47 PM
Super User
Posts: 19,815

Re: Delete letters/numbers from the observation in a particular variable

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;

Occasional Contributor
Posts: 10

Re: Delete letters/numbers from the observation in a particular variable


You're a treasure!

Respected Advisor
Posts: 3,156

Re: Delete letters/numbers from the observation in a particular variable

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

Super User
Posts: 19,815

Re: Delete letters/numbers from the observation in a particular variable

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

Contributor
Posts: 43

Re: Delete letters/numbers from the observation in a particular variable

Thank you Rizza, Pflickner and Hai.Kuo.

Than you So much.

Dipu

Super Contributor
Posts: 644

Re: Delete letters/numbers from the observation in a particular variable

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 511 views
  • 13 likes
  • 5 in conversation