topic Re: RLIKE in SAS EG in SAS Enterprise Guide

RLIKE in SAS EG

Linus69 — Wed, 22 May 2019 11:38:19 GMT

Hi!

We've just got access to SAS as a tool, and now I'm trying to translate my previous scripts from SQL.

I'm having trouble finding a commando like "rlike" - if anyone could please help me?
I've tried to make an example her of what I'm trying to do:

PROC SQL;
Create table test as
Select CreatedDate, DiscountCode, MemberId, ChangedFields
from my.source
where ChangedFIelds rlike 'discountCode:[0-9][0-9][0-9][0-9][0-9]->null'
ORDER BY CreatedDate;

So.. How to do this in SAS EG?

Re: RLIKE in SAS EG

Kurt_Bremser — Wed, 22 May 2019 12:09:23 GMT

SAS does not provide such an operator (which looks like a LIKE with regular expressions to me).

The straightforward thing to do (IMO) is using basic comparisons with substrings and testing for numeric data:

data have;
input changedfields $80.;
datalines;
discountCode:12345->null
discountCode:67890->null
discountCode:1x345->null
something else
;

data want;
set have;
where
  index(ChangedFIelds,'discountCode:') = 1 and
  notdigit(substr(ChangedFIelds,14,5)) = 0 and
  substr(ChangedFIelds,19,6) = '->null'
;
run;

Someone who is an expert on SAS perl regular expressions may come up with a more elegant solution.

Re: RLIKE in SAS EG

ChrisHemedinger — Wed, 22 May 2019 12:39:40 GMT

I wasn't sure about the whitespace situation between 'discountCode:' and the digits, so this might work:

data have;
input changedfields $80.;
datalines;
discountCode:12345->null
discountCode:67890->null
discountCode: 87654->null
discountCode:1x345->null
something else
;

proc sql;
 create table want as select changedfields from have
  where prxmatch('/discountCode:(\s*(\d\d\d\d\d))->null/',changedfields)>0;
quit;

Re: RLIKE in SAS EG

Linus69 — Thu, 23 May 2019 06:27:26 GMT

Thank you! That really sent me into the right direction