Desktop productivity for business analysts and programmers

PROC SQL issue

Accepted Solution Solved
Reply
Super Contributor
Super Contributor
Posts: 318
Accepted Solution

PROC SQL issue

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

 


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Super Contributor
Posts: 275

Re: PROC SQL issue

proc sql; select prxchange('s/^0{1,}|0{1,}$//',-1,strip(business_code)) as business_code from have; quit;

View solution in original post


All Replies
Solution
‎09-25-2015 06:23 AM
Super Contributor
Posts: 275

Re: PROC SQL issue

proc sql; select prxchange('s/^0{1,}|0{1,}$//',-1,strip(business_code)) as business_code from have; quit;
Respected Advisor
Posts: 4,609

Re: PROC SQL issue

Or...

proc sql;
select substr(bcString, findc(bcString, "0", "K"), 10) as business_code
from have;
PG
Super Contributor
Super Contributor
Posts: 318

Re: PROC SQL issue

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

 

 

Super Contributor
Posts: 336

Re: PROC SQL issue

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;

Frequent Contributor
Posts: 144

Re: PROC SQL issue

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

 

Respected Advisor
Posts: 4,609

Re: PROC SQL issue

I tested my code. It gives the expected result. The K modifier does exist, at least in version 9.4.
PG
Frequent Contributor
Posts: 144

Re: PROC SQL issue

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

Grand Advisor
Posts: 10,239

Re: PROC SQL issue

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 .

Super Contributor
Super Contributor
Posts: 318

Re: PROC SQL issue

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

 

Once again thanks a lot all...

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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