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!
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;
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.
Try to use the contains clause in SQL.
where store_name contains keyword
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.
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?
Hi Reeza,
No, my list is hardcoded.
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;
Thank you!
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;
@Ksharp, add suffix letter 'o' so that the matching pattern is compiled only once :
'/\b(COSTCO|APPLE|ABC)\b/io'
Thanks Ksharp, PGStats!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.