BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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...

 

1 ACCEPTED SOLUTION

Accepted Solutions
slchen
Lapis Lazuli | Level 10
proc sql; select prxchange('s/^0{1,}|0{1,}$//',-1,strip(business_code)) as business_code from have; quit;

View solution in original post

9 REPLIES 9
slchen
Lapis Lazuli | Level 10
proc sql; select prxchange('s/^0{1,}|0{1,}$//',-1,strip(business_code)) as business_code from have; quit;
PGStats
Opal | Level 21

Or...

proc sql;
select substr(bcString, findc(bcString, "0", "K"), 10) as business_code
from have;
PG
woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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

 

 

user24feb
Barite | Level 11

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;

arodriguez
Lapis Lazuli | Level 10

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")

 

PGStats
Opal | Level 21
I tested my code. It gives the expected result. The K modifier does exist, at least in version 9.4.
PG
arodriguez
Lapis Lazuli | Level 10

I have check my documentation and in 9.3 doesn't exist, so I guess that he use a 9.3 or bellow

ballardw
Super User

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 .

woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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...

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1548 views
  • 3 likes
  • 6 in conversation