topic Re: Extract characters in a field/cell in SAS Enterprise Guide

Extract characters in a field/cell

jen123 — Tue, 07 May 2013 17:22:56 GMT

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!

Re: Extract characters in a field/cell

Anotherdream — Tue, 07 May 2013 17:39:39 GMT

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

Re: Extract characters in a field/cell

AncaTilea — Tue, 07 May 2013 17:42:09 GMT

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;

Re: Extract characters in a field/cell

PGStats — Tue, 07 May 2013 18:15:07 GMT

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;

drop string i word;

datalines;

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

;

Re: Extract characters in a field/cell

Tom — Tue, 07 May 2013 18:18:05 GMT

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.

Re: Extract characters in a field/cell

jen123 — Tue, 07 May 2013 19:12:57 GMT

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

Re: Extract characters in a field/cell

Anotherdream — Tue, 07 May 2013 19:26:18 GMT

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;

Re: Extract characters in a field/cell

jgfitton — Fri, 20 Mar 2015 03:53:39 GMT

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