Hi friends, can someone please help on SQL,
is there a way in SQL to query this?
we need 10 digit numbers "business_code" starting with real number (1 to 9) but we are facing leading uncertain zeros and we need to delete them. "business_code" is 10 digit so we need to get only 10 digit code like;
Have
business_code
0001254872154000000
0095874502460000
02154986532
5498876521000
want
1254872154
9587450246
2154986532
5498876521
please help...
Or...
proc sql;
select substr(bcString, findc(bcString, "0", "K"), 10) as business_code
from have;
i am using PG code but getting this warning message and output is like this;
WARNING: In a call to the FINDC function or routine, the modifier "K" not valid.
output
0001254872
0095874502
0215498653
000
I think slchen's code is a good option. With a Perl-Expression you can kick out all the leading and trailing 0's while keeping the ones in the middle.
Data Have;
Input business_code $50.;
Datalines;
0001254872154000000
0095874502460000
02154986532
5498876521000
;
Run;
Proc SQL;
Create Table Want As Select *, PRXChange("s/(^0+|0+$)//",-1,Strip(business_code)) As BC_New From Have;
Quit;
The idea of PG i think that is to find the first digit not 0, to select the following 10 digits, so, "K" modifier doesn't exist, you could specify to search numbers from 1 to 9
replace
findc(bcString, "0", "K")
to
findc(bcString, "123456789")
I have check my documentation and in 9.3 doesn't exist, so I guess that he use a 9.3 or bellow
In the mode of "an ounce of prevention is worth a pound of cure", why does the data source have extra digits? If some system is not complying with the planned data layout/contents for this field, how many others may have less subtle issues? If this can be addressed at an earlier point then you may not have to worry about the times when you start getting unexpected characters in the middle of your "10 digit" code like 000123-4567888 .
Thanks a lot everyone for your input, this is really so informative. i am sorry but i forgot to mention that i am using sas 9.1 where lot of options would have unavailable like PG option.
Another thing is i have used Slchen solution and it worked 🙂 so thanks a lot....
Also, Balladrw - i understand your point but sometime things goes beyond our ablility to interfere and i think that's why we have experts like you all....:)
Once again thanks a lot all...
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.