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

how to find the nth string

HitmonTran — Thu, 26 Nov 2020 21:46:52 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. 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

Re: how to find the nth string

Reeza — Thu, 26 Nov 2020 21:40:58 GMT

Please expand your sample data to include records where the already provided solutions did not work. Otherwise you'll get the same answers again.
https://communities.sas.com/t5/SAS-Programming/how-to-find-the-nth-string/m-p/701893

Re: how to find the nth string

HitmonTran — Thu, 26 Nov 2020 21:46:14 GMT

i did provide the sample data above. it is different from the previous version with more inconsistent spaces.

Re: how to find the nth string

r_behata — Thu, 26 Nov 2020 22:23:12 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
;
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;

Re: how to find the nth string

ChrisNZ — Fri, 27 Nov 2020 01:54:39 GMT

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

Re: how to find the nth string

ChrisNZ — Fri, 27 Nov 2020 02:03:58 GMT

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

Re: how to find the nth string

HitmonTran — Fri, 27 Nov 2020 04:24:40 GMT

thanks, your code worked as well. happy holidays!