Hi
I have a small problem, I have a large data set and I want to take these values that have certain characters (in my case - prefixes). There are dozens of these prefixes so doing LIKE/CONTAINS each time does not only take space but is inefficient.. Prefixes usually look like that - NLLL - so 1 number and 3 letters. Is there a way to check for that pattern in like/contains coditions or just a way to have these conditions contain multiple values i.e. IN option with prefixes or a list of prefixes that could be checked instead of only one?
Thanks in advance
Yes, have a look at perl regular expressions - prxparse/prxmatch. You could also break it out a bit:
data want;
set have;
where (substr(PREFIX,1,1) in ('1','2','3'))
and (substr(PREFIX(2,3) in ('ABC','CBD'));
run;
If you have lots, then make a dataset of the combinations and use that to generate your code from:
data _null_;
set list_of_combinations end=last;
if _n_=1 then call execute('data want; set have; where prefix in ("'||strip(item)||'");
else call execute(',"'||strip(item)||'"');
if last then call execute('); run;');
run;
Yes, have a look at perl regular expressions - prxparse/prxmatch. You could also break it out a bit:
data want;
set have;
where (substr(PREFIX,1,1) in ('1','2','3'))
and (substr(PREFIX(2,3) in ('ABC','CBD'));
run;
If you have lots, then make a dataset of the combinations and use that to generate your code from:
data _null_;
set list_of_combinations end=last;
if _n_=1 then call execute('data want; set have; where prefix in ("'||strip(item)||'");
else call execute(',"'||strip(item)||'"');
if last then call execute('); run;');
run;
If you don't have too many different patterns then a Regular Expression could be quite efficient for coding. Eg. for your pattern - NLLL - it could be something like:
where prxmatch('/^\d[[:alpha:]]{3}/o',<your variable>)
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.