topic Re: PROC SQL: Remove initial zeros from an alphanumeric field in SAS Programming

PROC SQL: Remove initial zeros from an alphanumeric field

JavierBlanco — Mon, 28 Nov 2022 08:32:04 GMT

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.

Re: PROC SQL: Remove initial zeros from an alphanumeric field

PeterClemmensen — Mon, 28 Nov 2022 08:09:14 GMT

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;

Re: PROC SQL: Remove initial zeros from an alphanumeric field

JavierBlanco — Mon, 28 Nov 2022 08:17:39 GMT

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.

Re: PROC SQL: Remove initial zeros from an alphanumeric field

PeterClemmensen — Mon, 28 Nov 2022 08:33:59 GMT

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.

Re: PROC SQL: Remove initial zeros from an alphanumeric field

Kurt_Bremser — Mon, 28 Nov 2022 09:18:36 GMT

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.

Re: PROC SQL: Remove initial zeros from an alphanumeric field

Kurt_Bremser — Mon, 28 Nov 2022 09:22:36 GMT

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

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

Re: PROC SQL: Remove initial zeros from an alphanumeric field

s_lassen — Mon, 28 Nov 2022 10:11:25 GMT

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