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

Delete letters/numbers from the observation in a particular variable

Dipu — Thu, 30 Jan 2014 17:03:18 GMT

Hello,

Can please guide me-

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

One of the variable name-

VAR1 contains--

ased

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

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

Reeza — Thu, 30 Jan 2014 17:06:00 GMT

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.

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

Dipu — Thu, 30 Jan 2014 17:26:22 GMT

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

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

pflickner — Thu, 30 Jan 2014 17:44:49 GMT

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.

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

Reeza — Thu, 30 Jan 2014 17:47:59 GMT

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;

ased

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;

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

pflickner — Thu, 30 Jan 2014 17:50:11 GMT

You're a treasure!

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

Haikuo — Thu, 30 Jan 2014 18:01:32 GMT

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;

ased

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

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

Reeza — Thu, 30 Jan 2014 18:05:52 GMT

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

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

Dipu — Thu, 30 Jan 2014 18:43:32 GMT

Thank you Rizza, Pflickner and Hai.Kuo.

Than you So much.

Dipu

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

RichardinOz — Fri, 31 Jan 2014 08:38:17 GMT

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