Deleting characters at the end when the string has a delimiter and different lengths

[hein68]

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.

[ballardw · Accepted solution]

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.

[PGStats]

@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;

[ballardw]

---

@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.

[hein68]

That worked.

Thanks!