I need to extract the time range and location from a variable "Itemproperties". I'm having difficulty bc some location have 2 delimerters ("@") and others will have 1. Also the substr would not work bc the position of the characters are not consistent.
Data:
Data:
Itemproperties
Pickup Details:6. 11/21 SAT 4:40 - 5:00 @ Mountain View @ Ranch 99
Pickup Details:6. 11/21 SAT 5:30 - 5:50 @ Cupertino
Pickup Details:5. 10/23 FRI 4:40 - 5:00 @ W. SJ @ Starbucks
want:
location time
Mountain View @ Ranch 99 4:40 - 5:00
Cupertino 5:30 - 5:50
W. SJ @ Starbucks 4:40 - 5:00
data have;
infile cards truncover;
input Itemproperties $100. ;
cards;
Pickup Details:6. 11/21 SAT 4:40 - 5:00 @ Mountain View @ Ranch 99
Pickup Details:6. 11/21 SAT 5:30 - 5:50 @ Cupertino
Pickup Details:5. 10/23 FRI 4:40 - 5:00 @ W. SJ @ Starbucks
;
run;
data want;
set have;
location=strip(substr(Itemproperties,index(Itemproperties,'@')+1));
/* Regular Expression - Definition */
/* \d matches a digit (equal to [0-9]) */
/* : matches the character : literally */
/* \d+ matches a digit (equal to [0-9]) */
/* + Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy) */
/* \s matches any whitespace character */
/* - matches the character - literally */
/* \s matches any whitespace character */
/* \d matches a digit (equal to [0-9]) */
/* : matches the character : literally */
/* \d+ matches a digit (equal to [0-9]) */
/* + Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy) */
patternID = prxparse('/(\d:\d+\s-\s\d:\d+)/');
call prxsubstr(patternID, Itemproperties, position, length);
if position > 0 then time=substr(Itemproperties,position,length);
drop patternID position length;
run;
data have;
infile cards truncover;
input Itemproperties $100. ;
cards;
Pickup Details:6. 11/21 SAT 4:40 - 5:00 @ Mountain View @ Ranch 99
Pickup Details:6. 11/21 SAT 5:30 - 5:50 @ Cupertino
Pickup Details:5. 10/23 FRI 4:40 - 5:00 @ W. SJ @ Starbucks
;
run;
data want;
set have;
location=strip(substr(Itemproperties,index(Itemproperties,'@')+1));
/* Regular Expression - Definition */
/* \d matches a digit (equal to [0-9]) */
/* : matches the character : literally */
/* \d+ matches a digit (equal to [0-9]) */
/* + Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy) */
/* \s matches any whitespace character */
/* - matches the character - literally */
/* \s matches any whitespace character */
/* \d matches a digit (equal to [0-9]) */
/* : matches the character : literally */
/* \d+ matches a digit (equal to [0-9]) */
/* + Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy) */
patternID = prxparse('/(\d:\d+\s-\s\d:\d+)/');
call prxsubstr(patternID, Itemproperties, position, length);
if position > 0 then time=substr(Itemproperties,position,length);
drop patternID position length;
run;
Many ways to do this. Here are 2 examples. Arrange to suit.
data TEST;
infile cards truncover;
input ITEMPROPERTIES $100. ;
TIME1=scan(ITEMPROPERTIES,5,' ')||'-'||scan(ITEMPROPERTIES,7,' ');
LOC1 =substr(ITEMPROPERTIES,index(ITEMPROPERTIES,'@')+1);
TIME2=prxchange('s/.*(\d\d?:\d\d[ -]{1,3}\d\d?:\d\d).*/\1/',1,ITEMPROPERTIES);
LOC2 =prxchange('s/.*?@ *(.*)/\1/',1,ITEMPROPERTIES);
cards;
Pickup Details:6. 11/21 SAT 4:40 - 5:00 @ Mountain View @ Ranch 99
Pickup Details:6. 11/21 SAT 5:30 - 5:50 @ Cupertino
Pickup Details:5. 10/23 FRI 4:40 - 5:00 @ W. SJ @ Starbucks
run;
ITEMPROPERTIES | TIME1 | LOC1 | TIME2 | LOC2 |
---|---|---|---|---|
Pickup Details:6. 11/21 SAT 4:40 - 5:00 @ Mountain View @ Ranch 99 | 4:40-5:00 | Mountain View @ Ranch 99 | 4:40 - 5:00 | Mountain View @ Ranch 99 |
Pickup Details:6. 11/21 SAT 5:30 - 5:50 @ Cupertino | 5:30-5:50 | Cupertino | 5:30 - 5:50 | Cupertino |
Pickup Details:5. 10/23 FRI 4:40 - 5:00 @ W. SJ @ Starbucks | 4:40-5:00 | W. SJ @ Starbucks | 4:40 - 5:00 | W. SJ @ Starbucks |
Also note how more flexible the RegEx is. It can parse this data without issue (spaces removed around and inside time, and after @).
Pickup Details:5. 10/23 FRI4:40-5:00@W. SJ @ Starbucks
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.