DATA Step, Macro, Functions and more

How to set a user-defined pattern to be checked by like/contains condition

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

How to set a user-defined pattern to be checked by like/contains condition

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


Accepted Solutions
Solution
‎10-23-2014 05:41 AM
Super User
Super User
Posts: 7,401

Re: How to set a user-defined pattern to be checked by like/contains condition

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;

View solution in original post


All Replies
Solution
‎10-23-2014 05:41 AM
Super User
Super User
Posts: 7,401

Re: How to set a user-defined pattern to be checked by like/contains condition

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;

Respected Advisor
Posts: 3,890

Re: How to set a user-defined pattern to be checked by like/contains condition

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>)

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 173 views
  • 3 likes
  • 3 in conversation