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.
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
Thank you @HitmonTran Have i read the pattern correctly here? If yes, it's too simple 🙂
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
;
data want;
set have;
location=substr(Itemproperties,42);
time=substr(Itemproperties,29,12);
run;
Hi @HitmonTran Can you please paste the data as plain text rather than pics for users to copy paste?
Thank you @HitmonTran Have i read the pattern correctly here? If yes, it's too simple 🙂
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
;
data want;
set have;
location=substr(Itemproperties,42);
time=substr(Itemproperties,29,12);
run;
Actually so did I. I was thinking perhaps to construct a regular expression of sorts. However, either way I would still need to identify a pattern. That's when I noticed the heck in the pattern. Haha. Have a great thanksgiving holiday. Happy thanksgiving!!!
Can you post a few more records that varies plz?
Hi again @HitmonTran I took your updated sample from the other thread. I used COMPBL function for the updated sample-
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
;
data want;
set have;
location=substr(compbl(Itemproperties),42);
time=substr(compbl(Itemproperties),29,12);
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
;
data want;
set have;
time=prxchange('s/.*?(\d?\d:\d{2}\s-\s\d?\d:\d{2}\s(?=@)).*/$1/', -1, Itemproperties);
location=compbl(substr(Itemproperties,index(Itemproperties,'@')+1));
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
;
data want;
set have;
call scan(Itemproperties,2,p,l,'@');
location=substr(Itemproperties,p);
time=scan(substr(Itemproperties,1,p-1),-1,'@','a');
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.