Extract characters in a field/cell

[jen123]

Hello,

I have a cell in a dataset that is pipe delimited.  I need to extract the first 5 numbers and the last 5.

Cell data example:  18995|Pend-PrSu|18000_ProdSupReg|I|00102||||

To get the first 5, I used the formula:  substr(data, 1,5).  It returned 18995.

To get the last 5, I used the formula:  substr(data, -9,-4).  Unsuccessful.  I got blanks.

How can I get the last 5 numbers?

Thanks!

[Anotherdream · Accepted solution]

I think you want the last 5 variables before the ||||. If so, you do not want to start the 4th position from the right (because that will include the final |) but instead start at the 5th position, and go 5 positions left from there.

If you truly want just the last X characters, then you can do something like "reverse(substr(left(reverse(YOURVAR)),5,5))"

Remember in substr, the first arguement is your start and the second if your Total length to read AFTER your start, not your position you want to end at.

Let me know if you need further help, as there are ways to make this significantly more dynamic.

Brandon

[AncaTilea]

Hi, similarly (in some way) to what Anotherdream suggested, here is what I thought of:

data in;

    length string $50.;

    string = " 18995|Pend-PrSu|18000_ProdSupReg|I|00102||||";

    *remove the letters and other funny characters;

    temp_string = prxchange("s/[a-zA-Z|-]//", -1, string);

    *get the first 5 digits;

    string_first_5 = substr(temp_string, 1, 6);

    *get the last 5 digits;

    string_last_5 = translate(reverse(substr(reverse(trim(temp_string)), 1, 6)), "", "_");

run;

proc print data = in;run;

[PGStats]

Here is a fairly robust way to get what you want :

data in;

length string $100 word $50;

input string;

do i = 1 to countw(string, "|");

     word = scan(string, i, "|");

     if not anyalpha(word) and anydigit(word) then do;

          if missing(firstNumber) then firstNumber = input(word, ?? best.);

          else lastNumber = input(word, ?? best.);

          end;

     end;

drop string i word;

datalines;

18995|Pend-PrSu|18000_ProdSupReg|I|00102||||

;

PG

[Tom]

Looks like your data is delimited. Why not just use SCAN function?  By default it will treat multiple delimiters as one so you would want to use:

data _null_;

  string='18995|Pend-PrSu|18000_ProdSupReg|I|00102||||  ';

  first = scan(string,1,'|');

  last = scan(trim(string),-1,'|');

  put (_all_) (=);

run;

Trim is needed to prevent SCAN from treating the spaces at the end of the string as the right most delimited value.

[jen123]

The data in the column are pipe delimited.  Is it possible, within SAS EG 4.2, to do text-to-column based on "|" and ";"?

[Anotherdream]

Sure is. The below code will break your variable into X different observations, where X is the number of delimiters found within your string + 1. Note taht your string ends in ||||, so I am assuming that the values between tehse strings is a null (blank value) and that there is a blank value ending your string. If that is not true, this has to be changed a bit.

Also note I defined newvars to be 9 as a hardcoded value, so if you are going to use this code on different strings that have variable length you have to make modifications to this to make sure your array is large enough to handle the split out.

data newdata;

set olddata;

string='18995|Pend-PrSu|18000_ProdSupReg|I|00102||||  ' (replace this with whatever you called your variable);

array newvars (9) $;

do i=1 to countw(string,'|','m');

newvar(i)=strip(scan(string,i,'|','m'));

end;

run;

[jgfitton]

*Here is the code you need ;

data output ;

length newvars1-newvars11 $ 200;

set input;

array newvars (11) $;

do i=1 to countw(data,'|','m');

newvars(i)=strip(scan(data,i,'|','m'));

end;

run;