topic Re: How to look for a list of values in a character variable? in SAS Programming

How to look for a list of values in a character variable?

renjithr — Wed, 18 May 2016 17:01:34 GMT

Hi,

I have a character string where that contains the names of various stores and I would like to filter the observations based on certain store values.

I tried substring function but it does not work all the time.

Below is an example of what I am looking for.

Input file:

Store_names

ABC STORE

APPLE COMPUTERS

MICROSOFT CORPORATION

COSTCO STORE

WALMART

SAMS CLUB

Desired Output file

Store_names

ABC STORE

COSTCO

APPLE COMPUTERS

Please let me know.

Thank you!

Re: How to look for a list of values in a character variable?

Reeza — Wed, 18 May 2016 17:52:34 GMT

So you want to search but not for exact values?

I'm not sure what you need beyond the following:

data want;
set have;
where store_name in ('ABC STORE' 'COSTCO STORE' 'APPLE COMUTERS');
RUN;

Re: How to look for a list of values in a character variable?

renjithr — Wed, 18 May 2016 17:57:08 GMT

Thanks Reeza!

yes, I am not searching for the exact values.

for eg: I need to find APPLE COMPUTERS in the desired output file with a filter of 'APPLE'.

I tested the where conditon but it did not work.

Re: How to look for a list of values in a character variable?

PGStats — Wed, 18 May 2016 18:20:54 GMT

Try to use the contains clause in SQL.

where store_name contains keyword

Re: How to look for a list of values in a character variable?

renjithr — Wed, 18 May 2016 18:29:18 GMT

Thanks PG Stats!

Contians works but only with one value. It does not work in my test, where I checked for a list of values.

eg: where store_name in ('COSTCO') works.

but not ('COSTCO', 'APPLE', 'SAMS') - does not support I think.

Re: How to look for a list of values in a character variable?

Reeza — Wed, 18 May 2016 18:33:47 GMT

Unfortunately all of the Contains/Find/Index functions all work with a single argument.

ie contains ("APPLE") or contains ("COMPUTERS") or contains("COSTCO")

Do you have your list in a dataset?

Re: How to look for a list of values in a character variable?

renjithr — Wed, 18 May 2016 18:43:39 GMT

Hi Reeza,

No, my list is hardcoded.

Re: How to look for a list of values in a character variable?

Reeza — Wed, 18 May 2016 19:25:13 GMT

Here's an example using an array to loop through your words.

data have;
input stores $32.;
cards;
ABC STORE
APPLE COMPUTERS
MICROSOFT CORPORATION
COSTCO STORE
WALMART
SAMS CLUB
;
run;

data want;
array lookup(2) $ _temporary_  ("COSTCO" "APPLE" );

set have;
do i=1 to dim(lookup);
if find(stores, trim(lookup(i)))>0 then output;
end;
run;

Re: How to look for a list of values in a character variable?

renjithr — Wed, 18 May 2016 19:28:15 GMT

Thank you!

Re: How to look for a list of values in a character variable?

Ksharp — Thu, 19 May 2016 01:22:08 GMT

Reeza should use FINDW() .

Here is an alternative way .

data have;
input stores $32.;
cards;
ABC STORE
APPLE COMPUTERS
MICROSOFT CORPORATION
COSTCO STORE
WALMART
SAMS CLUB
;
run;

data want;
set have;
if prxmatch('/\b(COSTCO|APPLE|ABC)\b/i',stores);
run;

Re: How to look for a list of values in a character variable?

PGStats — Thu, 19 May 2016 01:56:15 GMT

@Ksharp, add suffix letter 'o' so that the matching pattern is compiled only once :

'/\b(COSTCO|APPLE|ABC)\b/io'

Re: How to look for a list of values in a character variable?

renjithr — Thu, 19 May 2016 15:14:36 GMT

Thanks Ksharp, PGStats!