I'm attempting to extract a substring from various areas of a string.
It would be on lines that have "save $x w/purchase" and would be looking for the quantity required to buy. In line 1, it would be "4". In line 8 in would be "6" The price after "save" can be double digit dollars (i.e. $25.00) and the quantity required can also be double digits. Sometimes it appears after "purchase/", somethings "purchase/any". I guess the easiest way to boil it down would be the first complete number (1, 5, 12, etc.) after the occurence of "w/". It seems like a straight forward function/regex but I can't seem to get one to work with all the possible permutations of order and number of positions.
Sample data would be
data WORK.SAMPLE;
infile datalines dsd truncover;
input Offer:$194.;
datalines4;
save $1.00 w/purchase/any 4 or more mix & match
save $0.73 w/Just For U (Limit: 1)
save $1.00 w/purchase/any 4 participating items mix & match
save $1.00 w/purchase/any 4 or more mix & match
save $1.00 w/purchase/any 4 participating items mix & match
"save $1.00 w/purchase/any 4 participating items mix & match, save $1.00 w/purchase/any 4 participating items mix & match"
save $4.00 w/purchase/4 or more mix or match
save $3.00 w/purchase/any6 participating items mix & match
save $4.00 w/purchase/any 4 participating items mix & match
save $3.00 (Limit: 5)
save $5.00 w/purchase/5 participating items mix & match
save $5.00 w/purchase/5
save $1.00 (Limit: 1)
save $1.00 (Limit: 1)
save $4.00 w/purchase/any 4 participating items mix & match
save $4.00 w/purchase/any 4 participating items mix & match
save $3.00 w/purchase/any 6 participating items mix & match
save $0.50 w/purchase/any 4 or more participating items mix & match
save $5.00 w/purchase/5 participating items mix & match
save $5.00 w/purchase/any 5 participating items mix & match
;;;;
data WORK.SAMPLE;
infile datalines dsd truncover;
input Offer:$194.;
datalines4;
save $1.00 w/purchase/any 4 or more mix & match
save $0.73 w/Just For U (Limit: 1)
save $1.00 w/purchase/any 4 participating items mix & match
save $1.00 w/purchase/any 4 or more mix & match
save $1.00 w/purchase/any 4 participating items mix & match
"save $1.00 w/purchase/any 4 participating items mix & match, save $1.00 w/purchase/any 4 participating items mix & match"
save $4.00 w/purchase/4 or more mix or match
save $3.00 w/purchase/any6 participating items mix & match
save $4.00 w/purchase/any 4 participating items mix & match
save $3.00 (Limit: 5)
save $5.00 w/purchase/5 participating items mix & match
save $5.00 w/purchase/5
save $1.00 (Limit: 1)
save $1.00 (Limit: 1)
save $4.00 w/purchase/any 4 participating items mix & match
save $4.00 w/purchase/any 4 participating items mix & match
save $3.00 w/purchase/any 6 participating items mix & match
save $0.50 w/purchase/any 4 or more participating items mix & match
save $5.00 w/purchase/5 participating items mix & match
save $5.00 w/purchase/any 5 participating items mix & match
;;;;
data want;
set sample;
pid=prxparse('/(?<=purchase\/)(any)?\s*\d+/i');
call prxsubstr(pid, offer, position, length);
if position ne 0 then
quantity=input(compress(substr(offer, position, length),,'kd'),best.);
drop pid;
run;
proc print;run;
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.