Scan multiple Delimiter

[Deb4]

So I am trying to delimit(/) an address variable using Scan function. But I only need to use the last delimiter and not the initial delimiters which are string.

For Eg: West of arrell street 1/2/SAN PEDRO CA 90731.
I want 'SAN PEDRO CA 9073' in a different column while 'West of arrell street 1/2' in another. But my code is considering both the (/) as delimiters.
How to write a scan function in such a way that it only considers the final (/) or initial(if reversed) as delimiters and ignore the rest of them?

[mkeintz · Accepted solution]

This is where using a negative value for the position parameter of the SCAN function is just the right tool to get the last component (because a negative number starts from the last position and works towards the first).  Then, to get the first component, apply TRANWRD to the original text to "translate" the last component to a blank.

data _null_;
  original_text="West of arrell street 1/2/SAN PEDRO CA 90731";
  last_component=scan(original_text,-1,'/');
  first_component=tranwrd(original_text,cats('/',last_component),'');
  put (_all_) (=);
run;

The only flaw in this would be if the string composing the last_component appears more than once, which would remove the first instance.   You can avoid this by locating the text position of last character that precedes the "/" that demarks the last component (actually one character to the left), then just copy a substring of the original text through that position:

data _null_;
  original_text="West of arrell street 1/2/SAN PEDRO CA 90731";
  last_component=scan(original_text,-1,'/');
  first_component=substr(original_text,1, length(original_text)-length(last_component)-1);
  put (_all_) (=);
run;

[Deb4]

Thank you 🙂

[FreelanceReinh]

Hello @Deb4 and welcome to the SAS Support Communities!

Just determine the position of that last delimiter (FINDC function) and then extract the two parts of the string using the SUBSTR function.

Example:

data have;
addr='West of arrell street 1/2/SAN PEDRO CA 90731';
run;

data want(drop=p);
set have;
p=findc(addr,'/','b');
addr1=substr(addr, 1, p-1);
addr2=left(substr(addr, p+1));
run;

I used the LEFT function to ensure that ADDR2 does not contain leading blanks. Feel free to define shorter lengths (than the default, which is the defined length of ADDR) for ADDR1 and ADDR2 with a LENGTH statement.

[Deb4]

That's an efficient solution. Thanks a lot 🙂