BookmarkSubscribeRSS Feed
renjithr
Quartz | Level 8

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!

11 REPLIES 11
Reeza
Super User

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;
renjithr
Quartz | Level 8

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.

PGStats
Opal | Level 21

Try to use the contains clause in SQL.

 

where store_name contains keyword

 

 

PG
renjithr
Quartz | Level 8

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.

Reeza
Super User

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?

renjithr
Quartz | Level 8

Hi Reeza,

 

No, my list is hardcoded.

Reeza
Super User

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

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;
PGStats
Opal | Level 21

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

 

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

PG
renjithr
Quartz | Level 8

Thanks Ksharp, PGStats!

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
  • 11 replies
  • 1527 views
  • 5 likes
  • 4 in conversation