topic Re: Regular Expressions: Extracting the price from a string in SAS Programming

Regular Expressions: Extracting the price from a string

Mgarret — Fri, 26 Jun 2015 18:54:11 GMT

Hi,

I’m trying to extract a price from a string and I’m having some with my code.

This is what I start with:

PROMOTION_TITLE

Aug Earn $25 v2 external

Aug Earns $125 v2 external

Aug Earns $50 v2 external

This is what I need:

Price

$25

$125

$50

This is my program so far. I can't seem to get the syntax right.

data test (keep=PROMOTION_TITLE price) ;

set a.promo;

price =(input(prxchange('s/(\w+)_(\d+)_(\w+)/$2/',-1,PROMOTION_TITLE),8.))/100;

format price dollar8.2;

run;

Any assistance will be greatly appreciated.

Thanks!

Re: Regular Expressions: Extracting the price from a string

sbb — Fri, 26 Jun 2015 19:32:13 GMT

Consider other SAS functions...

* DATA step with instream data-rows for illustration. ;

data _null_;

input ;

format price dollar8.2 ;

* parse input record and extract dollar-amount, convert to numeric. ;

price = input(scan(substr(_infile_,find(_infile_,'$','t')+1),1,' '),8.);

putlog _all_;

datalines;

Aug Earn $25 v2 external

Aug Earns $125 v2 external

Aug Earns $50 v2 external

run;

Scott Barry

SBBWorks, Inc.

Re: Regular Expressions: Extracting the price from a string

data_null__ — Fri, 26 Jun 2015 19:59:23 GMT

This looks about right. Instead of removing everything around the dollar amount use a capture buffer to retrieve it.

data test;
   input PROMOTION_TITLE $80.; 
   retain rx;
   if _n_ eq 1 then rx = prxparse('/(\$\d+\.?\d?\d?)/');

   if prxmatch(rx,promotion_title) then price = prxposn(rx,1,promotion_title);
   cards; 
Aug Earn $25 v2 external 
Aug Earns $125.75x v2 external 
Aug Earns $50 v2 external 
;;;;
   run; 
proc print; 
   run; 

Re: Regular Expressions: Extracting the price from a string

slchen — Fri, 26 Jun 2015 20:10:41 GMT

data want;

input ;

price =prxchange('s/.*(\$\d+).*/\1/',-1,_infile_) ;

datalines;

Aug Earn $25 v2 external

Aug Earns $125 v2 external

Aug Earns $50 v2 external

run;

Re: Regular Expressions: Extracting the price from a string

Mgarret — Fri, 26 Jun 2015 20:12:26 GMT

This is good but is it possible to change the var to numeric?

Re: Regular Expressions: Extracting the price from a string

data_null__ — Fri, 26 Jun 2015 20:24:45 GMT

Yes.

Re: Regular Expressions: Extracting the price from a string

PGStats — Sat, 27 Jun 2015 05:43:29 GMT

My proposition is similar to DN's :

data have;

input pt &:$32.;

datalines;

Aug Earn $25 v2 external

Aug Earns $125 v2 external

Aug Earns $50 v2 external

;

data want;

if not prxId then prxId + prxparse("/\$\s?\d+(\.\d{2})?/");

set have;

call prxsubstr(prxId, pt, pos, len);

if pos > 0 then price = input (substr(pt, pos, len), dollar10.);

format price dollar11.2;

drop prxId pos len;

run;

proc print data=want noobs; run;

Re: Regular Expressions: Extracting the price from a string

Peter_C — Sat, 27 Jun 2015 08:07:13 GMT

There is a feature in proc format that enableS the definition of the prx in a INVALUE range

The feature has been there for a few years. Rick Langston has been demonstrating it at SAS GF (along with the concept of using a function in "label" area.)

Once compiled the syntax to pull that "price" could just be the usual INPUT statement with the very unusual user INFORMAT.

Re: Regular Expressions: Extracting the price from a string

Patrick — Sat, 27 Jun 2015 08:56:31 GMT

Thanks for this pointer to informats and RegEx: https://support.sas.com/resources/papers/proceedings12/245-2012.pdf

Re: Regular Expressions: Extracting the price from a string

Peter_C — Sat, 27 Jun 2015 09:56:48 GMT

Patrick

you're welcome. And thanks for reporting the link.

Of course, it doesn't make prx any simpler just their implementation

regards

Peter

Re: Regular Expressions: Extracting the price from a string

FriedEgg — Sat, 27 Jun 2015 14:33:18 GMT

data have;

input pt &:$32.;

datalines;

Aug Earn $25 v2 external

Aug Earns $125 v2 external

Aug Earns $50 v2 external

Aug Earns $125.01 v2 external

Aug Earns $50x v2 external

;

You can parse using double scan (it will be faster than prx)

data want;

set have;

price=input(scan(scan(pt,2,'$'),1,,'as'),best.);

format price dollar8.2;

Alternatively, using prx function

data want2;

re=prxparse('#(?<=\$)(\d+(\.\d{2})?)#');

format price dollar8.2;

do until(done);

set have end=done;

if prxmatch(re,pt) then do;

price=input(prxposn(re,1,pt),best.);

output;

end;

run;

Using a regexpe informat

proc format;

invalue ReDollar 's#^.*(?<=\$)(\d+(\.\d{2})?).*$#\1#' (regexpe) = [14.];

run;

data want3;

set have;

price=input(pt,ReDollar.);

format price dollar8.2;

run;

^.*(?<=\$)(\d+(\.\d{2})?).*$

Options: ^ and $ match at line breaks

Assert position at the beginning of a line (at beginning of the string or after a line break character) «^»

Match any single character that is not a line break character «.*»

Between zero and unlimited times, as many times as possible, giving back as needed (greedy) «*»

Assert that the regex below can be matched, with the match ending at this position (positive lookbehind) «(?<=\$)»

Match the character “$” literally «\$»

Match the regular expression below and capture its match into backreference number 1 «(\d+(\.\d{2})?)»

Match a single digit 0..9 «\d+»

Between one and unlimited times, as many times as possible, giving back as needed (greedy) «+»

Match the regular expression below and capture its match into backreference number 2 «(\.\d{2})?»

Between zero and one times, as many times as possible, giving back as needed (greedy) «?»

Match the character “.” literally «\.»

Match a single digit 0..9 «\d{2}»

Exactly 2 times «{2}»

Match any single character that is not a line break character «.*»

Between zero and unlimited times, as many times as possible, giving back as needed (greedy) «*»

Assert position at the end of a line (at the end of the string or before a line break character) «$»

Re: Regular Expressions: Extracting the price from a string

PGStats — Sat, 27 Jun 2015 17:28:28 GMT

Thanks Patrick and Peter, very good to know. I wish ALL the features of my licenced SAS software were described in the main doc.I find proc format particularly poor in that respect.

Re: Regular Expressions: Extracting the price from a string

Patrick — Sat, 27 Jun 2015 22:12:15 GMT

The question is now if these are unsupported features or just something missing in the documentation. I've asked SAS TechSupport this question and will post the answer here.

And here how this could work using the REGEXPE option:

proc format;

invalue Price (default=20)

's/.*\$(\d+\.?\d*).*/\1/' (REGEXPE) = [best32.]

other=0

;

run;

data have;

input pt &:Price.;

datalines;

Aug Earn $25 v2 external

Aug Earns $125.45 v2 external

Aug Earns $50 v2 external

Aug Earns v2 external

;

run;

Re: Regular Expressions: Extracting the price from a string

PGStats — Sun, 28 Jun 2015 04:47:04 GMT

After a bit of experimenting with the PRX - INFORMAT technique, starting with 's third example, I came to :

proc format;

invalue ReDollar (default=32767)

's#.*\$\s?(\d+(\.\d{2})?).*#\1#' (regexpe) = [12.];

run;

data want3;

set have;

price=input(pt, ReDollar.);

format price dollar8.2;

run;

I dropped the non-capturing group and the begin-end assertions from the pattern since they are not required. I changed the label informat dollar8.2 to 12. as it divided the price by 100. Most importantly, I specified a large default size for the informat; otherwise, longer input strings were truncated.

Re: Regular Expressions: Extracting the price from a string

FriedEgg — Sun, 28 Jun 2015 05:22:47 GMT

It is important to note that in the case where a variable this format would be applied to includes more that one valid dollar value, the last one will be returned. This is true of all the examples: mine, ,

This is because of the use of the greedy match at the beginning (.*), if you alternatively wanted to retrieve the first match, you could modify this to be a lazy match (.*?)

Re: Regular Expressions: Extracting the price from a string

Patrick — Mon, 29 Jun 2015 23:01:58 GMT

The answer I've got from SAS TechSupport: REGEXP and REGEXPE are supported features. The SAS documentation will get updated and they will be fully documented in the future.

Re: Regular Expressions: Extracting the price from a string

PGStats — Tue, 30 Jun 2015 01:03:07 GMT

Thank you Patrick! According to Rick Langston's paper, the feature was introduced with SAS 9.3. Makes me wonder how many goodies I am missing. Maybe the slow disclosure rate is just another step in the testing process.