topic PROC SQL issue in SAS Enterprise Guide

PROC SQL issue

woo — Tue, 22 Sep 2015 23:25:42 GMT

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

Re: PROC SQL issue

slchen — Wed, 23 Sep 2015 00:24:33 GMT

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

Re: PROC SQL issue

PGStats — Wed, 23 Sep 2015 00:59:45 GMT

Or...

proc sql;
select substr(bcString, findc(bcString, "0", "K"), 10) as business_code
from have;

Re: PROC SQL issue

woo — Wed, 23 Sep 2015 05:19:28 GMT

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

Re: PROC SQL issue

user24feb — Wed, 23 Sep 2015 08:40:15 GMT

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;

Re: PROC SQL issue

arodriguez — Wed, 23 Sep 2015 08:40:47 GMT

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

findc(bcString, "123456789")

Re: PROC SQL issue

PGStats — Wed, 23 Sep 2015 14:35:08 GMT

I tested my code. It gives the expected result. The K modifier does exist, at least in version 9.4.

Re: PROC SQL issue

ballardw — Wed, 23 Sep 2015 15:09:40 GMT

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 .

Re: PROC SQL issue

arodriguez — Wed, 23 Sep 2015 15:32:52 GMT

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

Re: PROC SQL issue

woo — Wed, 23 Sep 2015 16:05:06 GMT

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