BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dipu
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

9 REPLIES 9
Reeza
Super User

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
Calcite | Level 5

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
Fluorite | Level 6

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
Super User

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;

Haikuo
Onyx | Level 15

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
Super User

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

Dipu
Calcite | Level 5

Thank you Rizza, Pflickner and Hai.Kuo.

Than you So much.

Dipu

RichardinOz
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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