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!
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
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
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;
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
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.
The data in the column are pipe delimited. Is it possible, within SAS EG 4.2, to do text-to-column based on "|" and ";"?
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;
*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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.