Hello. I need to delete characters at the end when the string has a delimiter and different lengths. Here are two examples of my data values:
114wr01_ws02_2_3_MA_6_27_17
123abr01_abs03_2_1_CC_7_12_17
I need to delete the 4th underscore and everything after it. What would be the easiest way to do this?
Thanks.
Don't know if this is the easiest but the code is relatively short:
data example; x='114wr01_ws02_2_3_MA_6_27_17'; call scan(x,4,pos,l,'_'); y = substr(x,1,pos); drop pos l; run;
I create a new variable with the SUBSTR function so you can check if the result is what you need.
call scan returns both the position and length of the of the nth, the 4 above, word delimited by characters in the last list.
Drop removes the temporary variables created by call scan after use.
Don't know if this is the easiest but the code is relatively short:
data example; x='114wr01_ws02_2_3_MA_6_27_17'; call scan(x,4,pos,l,'_'); y = substr(x,1,pos); drop pos l; run;
I create a new variable with the SUBSTR function so you can check if the result is what you need.
call scan returns both the position and length of the of the nth, the 4 above, word delimited by characters in the last list.
Drop removes the temporary variables created by call scan after use.
@ballardw , your code returns up to the first character of the fourth word. What's required is more like:
data example;
do x= '114wr01_ws02_2_3xx_MA_6_27_17', '114wr01_ws02_2_3x', '114wr01_ws02_2';
call scan(x, 4, pos,len,'_');
if pos > 0 then y = substr(x, 1, pos+len-1);
else y = x;
output;
end;
drop pos len;
run;
@PGStats wrote:
@ballardw , your code returns up to the first character of the fourth word. What's required is more like:
data example; do x= '114wr01_ws02_2_3xx_MA_6_27_17', '114wr01_ws02_2_3x', '114wr01_ws02_2'; call scan(x, 4, pos,len,'_'); if pos > 0 then y = substr(x, 1, pos+len-1); else y = x; output; end; drop pos len; run;
Agreed if there are cases where there are not 4 "words".
Examples did not indicate this to be the case.
That worked.
Thanks!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.