topic Re: Help in substring function in SAS Programming

Help in substring function

Kalai2008 — Wed, 02 Aug 2017 18:45:30 GMT

data want;
set have (firstobs=1 obs=10);
id1=SUBSTRN(id,13,10);
RUN;

given data:

ABC00000012365948952321
ABC00000000000948952321
ABC00000000025369214558

Output want:

12365948952321

948952321

25369214558

I have set of ID's that consist of 23 digits.
First 3 letters starts with alphabets and the rest are numbers.
I want to pull only the last numbers that starts after zero.
Since it's not constant position not sure how to use the function?

Re: Help in substring function

Reeza — Wed, 02 Aug 2017 18:46:24 GMT

Use COMPRESS to remove any characters that are letters.

Use INPUT to read the remaining text as a number.

Re: Help in substring function

novinosrin — Wed, 02 Aug 2017 19:04:07 GMT

data have;
input var $25.;
datalines;
ABC00000012365948952321
ABC00000000000948952321
ABC00000000025369214558
;

data want;
set have;
new_var=compress(var,'abcdefghijklmnopqrstuvwxyz0');
run;

Regards,

Naveen Srinivasan

Re: Help in substring function

Reeza — Wed, 02 Aug 2017 19:04:05 GMT

@novinosrin It's not shown in the OP's example, but what if there was a 0 in the middle of the number? I think it's highly likely that's a possibility.

ABC00000012365948052321
ABC00000000000948952321
ABC00000000025369214008

Re: Help in substring function

Kalai2008 — Wed, 02 Aug 2017 19:07:30 GMT

Thank you, I tried the compress function, but as you said its eliminating the 0 .

example:

ABC000000000025225250

ABC000000000025205250

its eliminating the last zero and middle zero as well.

Re: Help in substring function

Reeza — Wed, 02 Aug 2017 19:08:47 GMT

@Kalai2008 wrote:

Thank you, I tried the compress function, but as you said its eliminating the 0 .

example:

ABC000000000025225250

ABC000000000025205250

its eliminating the last zero and middle zero as well.

POST YOUR CODE. You can specify digits or characters using the 3rd or 4th parameter of the COMPRESS function, you don't have to list them at all.

Re: Help in substring function

Kalai2008 — Wed, 02 Aug 2017 19:12:24 GMT

data chk;
set test1.CHK2(firstobs=1 obs=10);
acct=compress(acct_id,'ABC0');
RUN;

DATA:

ABC000000000014256320

ABC000000000140252301

Output Results :

1425632

1425231

Re: Help in substring function

kiranv_ — Wed, 02 Aug 2017 19:36:37 GMT

Something like this. What we are doing is making three groups. Anything mentioned in parentheis is group.1 st group is alphabets 2nd group is anything with 0 i.e. in midddle third group is from 0-9. We are replacing everything with last group that is third group mentioned as $3

data abc;
input var $50.;
datalines;
ABC00000012365948952321
ABC00000000000948952321
ABC00000000025369214558
ABC000000000253692145580
;


data bde;
set abc;
var1 =prxchange('s/([A-Z]+)(0+)([0-9]+)/$3/i', -1, var);
run;

Re: Help in substring function

Kalai2008 — Wed, 02 Aug 2017 19:38:13 GMT

Wow..It worked..Thanks a lot! Saved my time! Good & clear Explanation.

Re: Help in substring function

Reeza — Wed, 02 Aug 2017 20:11:58 GMT

PRX is a great solution, but which one of these can you understand and modify if necessary?

data want;
set abc;

*Keep only digits in the string;
x=compress(var, , 'kd');

*convert to numeric;
y=input(x, best32.);

*use substring to extract all text after 3/4th character and convert to number;
z=input(substr(var, 4), best32.);

*PRX formula;
w=prxchange('s/([A-Z]+)(0+)([0-9]+)/$3/i', -1, var);

format  y z  best32.;
format x w $32.;

run;

Re: Help in substring function

Kalai2008 — Wed, 02 Aug 2017 20:14:19 GMT

Somehow the code didn't picked this id,
It excluded the last zero.
ABCD0000000011423744160
ABCD0000000011129735160..

Anyways Thank you Reeza, will try to modify as per your solution.

Re: Help in substring function

Kalai2008 — Wed, 02 Aug 2017 20:15:08 GMT

Thank you Reeza. Let me try everything

Re: Help in substring function

Reeza — Wed, 02 Aug 2017 20:17:08 GMT

@Kalai2008 wrote:
Somehow the code didn't picked this id,
It excluded the last zero.
ABCD0000000011423744160
ABCD0000000011129735160..

Anyways Thank you Reeza, will try to modify as per your solution.

Note that I don't understand PRX, so the function used above is as the original poster posted. It's a perfectly valid solution, just not easy to modify especially as a beginner.

Re: Help in substring function

Kalai2008 — Wed, 02 Aug 2017 20:18:40 GMT

I am so sorry, the code worked . I did a mistake in the input function ( char to num). Thank you!

Re: Help in substring function

Ksharp — Sat, 05 Aug 2017 13:29:46 GMT

data abc;
input var $50.;
datalines;
ABC00000012365948952321
ABC00000000000948952321
ABC00000000025369214558
ABC000000000253692145580
;


data bde;
set abc;
var1 =prxchange('s/^[A-Z0]+//i', -1, var);
run;

Re: Help in substring function

Tom — Sat, 05 Aug 2017 16:38:23 GMT

If the maximum number you actually have is smaller than the maximum possible integer that SAS can represent exactly;

586   data _null_;
587     x=constant('exactint');
588     put x= comma23.;
589   run;

x=9,007,199,254,740,992

Then you can just read the digits into a number and put it back out as a string.

id1=put(input(substr(id,4),20.),20.-l);

But if you really have 20 decimal digits then that is too long to put into a number. So instead using the VERIFY() funciton to find the location of the first non-zero digit. Let's try both for some dummy data.

data want;
  input id $23.;
  id2=substr(id,4);
  if verify(trim(id2),'0') then id2=substr(id2,verify(id2,'0'));
  else id2='0';
  id3=put(input(substr(id,4),20.),20.-l);
  match=(id2=id3);
cards;
ABC00000012360948952321
ABC00000000000948902321
ABC00000000025369214558
ABC00000000000000000000
ABC12345678901234567890
;