how to find the nth string

[HitmonTran]

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

[novinosrin]

Hi @HitmonTran  Can you please paste the data as plain text rather than pics for users to copy paste?

[HitmonTran]

updated, thanks for looking into this

[novinosrin · Accepted solution]

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;

[HitmonTran]

gosh, i think i was thinking too technical, thanks!

[novinosrin]

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!!!

[HitmonTran]

ok sorry, the substr function doesn't work. The position is not consistent throughout all records. It only worked in the example above

[novinosrin]

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;

[HitmonTran]

still not working, for example 12:00 will have an extra position compared to 2:00, thanks for looking into this during the holiday

[novinosrin]

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;

[Ksharp]

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;