DATA Step, Macro, Functions and more

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

Reply
Frequent Contributor
Posts: 122

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

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!

Super User
Posts: 17,750

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

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;
Frequent Contributor
Posts: 122

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

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.

Respected Advisor
Posts: 4,641

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

Try to use the contains clause in SQL.

 

where store_name contains keyword

 

 

PG
Frequent Contributor
Posts: 122

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

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.

Super User
Posts: 17,750

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

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?

Frequent Contributor
Posts: 122

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

Hi Reeza,

 

No, my list is hardcoded.

Super User
Posts: 17,750

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

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;
Frequent Contributor
Posts: 122

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

Thank you!

Super User
Posts: 9,662

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

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;
Respected Advisor
Posts: 4,641

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

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

 

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

PG
Frequent Contributor
Posts: 122

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

[ Edited ]

Thanks Ksharp, PGStats!

Ask a Question
Discussion stats
  • 11 replies
  • 473 views
  • 5 likes
  • 4 in conversation