topic Re: how to find the nth string in SAS Programming

how to find the nth string

HitmonTran — Thu, 26 Nov 2020 20:43:19 GMT

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

Re: how to find the nth string

novinosrin — Thu, 26 Nov 2020 20:39:13 GMT

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

Re: how to find the nth string

HitmonTran — Thu, 26 Nov 2020 20:42:20 GMT

updated, thanks for looking into this

Re: how to find the nth string

novinosrin — Thu, 26 Nov 2020 20:50:24 GMT

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;

Re: how to find the nth string

HitmonTran — Thu, 26 Nov 2020 21:06:54 GMT

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

Re: how to find the nth string

novinosrin — Thu, 26 Nov 2020 21:09:14 GMT

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

Re: how to find the nth string

HitmonTran — Thu, 26 Nov 2020 21:32:56 GMT

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

Re: how to find the nth string

novinosrin — Thu, 26 Nov 2020 21:56:31 GMT

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;

Re: how to find the nth string

HitmonTran — Thu, 26 Nov 2020 22:01:15 GMT

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

Re: how to find the nth string

novinosrin — Thu, 26 Nov 2020 22:28:27 GMT

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;

Re: how to find the nth string

Ksharp — Fri, 27 Nov 2020 13:25:01 GMT

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;