PROC SQL: Remove initial zeros from an alphanumeric field

[JavierBlanco]

I need to remove some initial zeros from a field (it appears as an alphanumeric one in the DB) like this:

cod_acometida
000000000003391901
000000000008271401
000000000007696901
000000000005504701
000000000002298401
000000000000332701
000000000013942801
etc.

It's a variable number of characters but they are always zeros at the beginning of the string. I'm new at SAS, not sure if RegEx is applicable.

I'm using Enterprise Guide 7.15.

Thanks in advance.

[PeterClemmensen]

Try this

data have;
input cod_acometida :$20.;
datalines;
000000000003391901
000000000008271401
000000000007696901
000000000005504701
000000000002298401
000000000000332701
000000000013942801
;

data want;
   set have;
   cod_acometida = substr(cod_acometida, verify(cod_acometida, '0'));
run;

[JavierBlanco]

Thanks for your answer, but cod_acometida has many rows, not just the few ones that I put as an example. I'm trying to handle it as a SQL query within a

PROC SQL;

***

QUIT;

Actually I need to use that cod_acometida (but 'zeroless') in a JOIN clause; it appears without zeros in the other table.

Thanks in advance.

[PeterClemmensen · Accepted solution]

You can use the exact same expression in a Proc SQL Step. If you want to use the value without zeros in a join, simply use the 

substr(cod_acometida, verify(cod_acometida, '0'))

part directly in the join. 

[Kurt_Bremser]

The DATA step with DATALINES is just there to make usable example data (please remember this term and method, and use it to present example data in your future questions) out of the text you posted. It is not needed for your real data, which should already be in a SAS dataset.

Before you attempt the join, make sure that the variable types match, and see if you have leading blanks in the "other" dataset.

[Kurt_Bremser]

PS another method to get rid of the zeroes is a short departure into numeric:

cod_acometida = put(input(cod_acometida,32.),32. -l);

[s_lassen]

@Kurt_Bremser :

The method suggested

cod_acometida = put(input(cod_acometida,32.),32. -l);

will work for the data shown, but if the string has more than 15 significant digits (it is, after all, 19 digits long) you may get rounding errors.