Desktop productivity for business analysts and programmers

Extract characters in a field/cell

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 94
Accepted Solution

Extract characters in a field/cell

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!


Accepted Solutions
Solution
‎05-07-2013 01:39 PM
Super Contributor
Posts: 418

Re: Extract characters in a field/cell

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

View solution in original post


All Replies
Solution
‎05-07-2013 01:39 PM
Super Contributor
Posts: 418

Re: Extract characters in a field/cell

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

Super Contributor
Posts: 543

Re: Extract characters in a field/cell

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;

Respected Advisor
Posts: 4,606

Re: Extract characters in a field/cell

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

PG
Super User
Super User
Posts: 6,317

Re: Extract characters in a field/cell

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.

Frequent Contributor
Posts: 94

Re: Extract characters in a field/cell

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

Super Contributor
Posts: 418

Re: Extract characters in a field/cell

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;

N/A
Posts: 1

Re: Extract characters in a field/cell

*Here is the code you need Smiley Happy;

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 5314 views
  • 4 likes
  • 6 in conversation