topic Re: Extract just number part from alphanumeric string in SAS in SAS Programming

Extract just number part from alphanumeric string in SAS

buddha_d — Fri, 30 Sep 2022 14:45:40 GMT

Hi SAS community,

I have this scenario, where I need to extract account numbers from a string and it is messy and need help to fix it.

eg:

data have;
input accountnumber $50.;
cards;
pcg1234500008765CheckingPCG000006878siim
OAK000000345697000000CHECK
MMTAYL8202206ERRA23124999GIG
YYY2356543ach78Y34????
;
run;

WANT
1234500008765CheckingPCG000006878
000000345697000000
8202206ERRA23124999
2356543ach78Y34

I tried to use anydigit and substring function to start the right number, but I can't finish how to substring to get to the last digit?

Can someone help?

thanks

Re: Extract just number part from alphanumeric string in SAS

svh — Fri, 30 Sep 2022 15:43:58 GMT

The data you want still has letters in it--is there a systematic approach to the letters you are trying to keep? The COMPRESS() function has a handy 3rd argument for determining which kinds of characters to keep, but I'm not sure if it applies in this case.

https://documentation.sas.com/doc/en/pgmsascdc/v_031/fedsqlref/n01p24mgwawxa2n1hy4gwe5t59xf.htm

Re: Extract just number part from alphanumeric string in SAS

ballardw — Fri, 30 Sep 2022 16:08:53 GMT

Rules for why in

pcg1234500008765CheckingPCG000006878siim

you remove the first pcg but keep the second PCG are going to complicate this a lot.

Do you have any documentation about the components of that obnoxious string from the source?

That you can share?

Re: Extract just number part from alphanumeric string in SAS

buddha_d — Fri, 30 Sep 2022 16:26:34 GMT

I can't use the compress function as it eliminates even the letters in between account number. some account numbers are masked with letters for security reasons. So, this might not be an option.
Thanks for responding.

Re: Extract just number part from alphanumeric string in SAS

buddha_d — Fri, 30 Sep 2022 16:29:56 GMT

Ballardw,
This is just dummy data that I created. The real production data is messy. I just observed some pattern and posted it. I do need to extract the starting numerical digit (along with any letters are there in between) and ending numerical digit. How could I get it? I could get the starting numerical value for all the values, but to get the entrire string till the last number digit is the real challenge. Could someone help me in that please?