topic extract substrings from a string variable in SAS Programming

extract substrings from a string variable

DivyaGadde — Thu, 12 Mar 2020 14:15:34 GMT

Hello,

I am trying to extract multiple substrings from a string.

For example, I have a variable called Itinerary = Orlando, United States (North America) Start Date: 03/30/2020 - End Date: 04/01/2020|Fort Pierce, United States (North America) Start Date: 04/01/2020 - End Date: 04/07/2020|Fort Lauderdale, United States (North America) Start Date: 04/07/2020 - End Date: 04/07/2020

I would like to pull the substrings which are before and after the special character comma (,) So I would like to have a variable " location" which has Orlando, Unitedstates

Fort Pierce, United States

Fort Lauderdale, United States.

I have tried using Scan function but am able to pull just Orlando, United states but no the rest of the locations.

Any help would be highly appreciated.

Thanks.

Re: extract substrings from a string variable

Kurt_Bremser — Thu, 12 Mar 2020 14:26:37 GMT

Is this whole data in one observation, or did you just put in the pipe characters to show ends of records?

If it's one observation, see here:

data have;
infile datalines truncover;
input itinerary $500.;
datalines;
Orlando, United States (North America) Start Date: 03/30/2020 - End Date: 04/01/2020|Fort Pierce, United States (North America) Start Date: 04/01/2020 - End Date: 04/07/2020|Fort Lauderdale, United States (North America) Start Date: 04/07/2020 - End Date: 04/07/2020 
;

data want (keep=location);
set have;
length location $100;
do i = 1 to countw(itinerary,'|');
  location = scan(scan(itinerary,i,'|'),1,'(');
  output;
end;
run;

Re: extract substrings from a string variable

mkeintz — Thu, 12 Mar 2020 14:32:06 GMT

Is the location component following the comma (e.g. "United States") always followed by an open parenthesis? If so, and if the location component preceding the comma ("Orlando") is always at the beginning of the string, then the open paren is, operationally speaking, the real special character, as in:

 location=scan(strng,1,'(');

This just retrieve the first "word" in the string, where each word is all the text between (or preceding or following) the separator - in this case the "(" character.