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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.