BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Deb4
Fluorite | Level 6
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?
1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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;

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

4 REPLIES 4
mkeintz
PROC Star

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;

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Deb4
Fluorite | Level 6
Thank you 🙂
FreelanceReinh
Jade | Level 19

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
Fluorite | Level 6
That's an efficient solution. Thanks a lot 🙂

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 4420 views
  • 2 likes
  • 3 in conversation