topic Re: removing leading zeros function in SAS Enterprise Guide

removing leading zeros function

RENATA1 — Mon, 14 May 2018 19:10:58 GMT

I am creating some global variables:

%LET POL_NBR = '000742196','00012345';

and I want to create same exact one but without leading zeros, ex:

%LET POL_NBR2 = '742196','12345'; - what function can I use to achieve this?

thank you!!!

Re: removing leading zeros function

Reeza — Mon, 14 May 2018 19:15:47 GMT

I would use the INPUT() function to convert it to a number and then that will automatically remove the leading zeroes.

@RENATA1 wrote:

I am creating some global variables:

%LET POL_NBR = '000742196','00012345';

and I want to create same exact one but without leading zeros, ex:

%LET POL_NBR2 = '742196','12345'; - what function can I use to achieve this?

thank you!!!

Re: removing leading zeros function

novinosrin — Mon, 14 May 2018 19:17:30 GMT

%LET POL_NBR = '000742196','00012345';

%let POL_NBR=%sysfunc(compress(%bquote(&POL_NBR),0));
%put POL_NBR=&POL_NBR;

Re: removing leading zeros function

RENATA1 — Mon, 14 May 2018 19:22:49 GMT

Thank you soo much for your solution!! would you by any chance know if there is an option to remove only first three leading zeros, and if there is a zero in a forth or fifth position, keep those?

Re: removing leading zeros function

Tom — Mon, 14 May 2018 19:33:40 GMT

How did you creating the original macro variable that has the leading zeros?

It would be easier to fix it before the quotes and commas are added.

If the string is as regular as your example then you could probably generate a regular expression that would search for single quote followed by one or more zero and than other digit and remove the zeros.

But if the string is "hand crafted" you might have better luck processing each element separately with a macro "function".

%macro fix (value);
%local i word return sep ;
%do i=1 %to %sysfunc(countw(&value,%str(,),mq));
  %let word=%sysfunc(dequote(%scan(&value,&i,%str(,),mq)));
  %let word=%substr(&word,%sysfunc(verify(&word,0)));
  %let return=&return.&sep.%sysfunc(quote(&word,%str(%')));
  %let sep=,;
%end;
&return.
%mend fix ;

%LET POL_NBR = '000742196','00012345';
%put POL_NBR=|&pol_nbr| ;
%put want=|%fix(%bquote(&pol_nbr))|;

Re: removing leading zeros function

Tom — Mon, 14 May 2018 19:25:40 GMT

@RENATA1 wrote:

Thank you soo much for your solution!! would you by any chance know if there is an option to remove only first three leading zeros, and if there is a zero in a forth or fifth position, keep those?

Please explain it more detail. Normally with numbers you count places from the decimal point. So are you saying your values are all less than 100,000? So that after converting to a number you want them re-converted back to strings using the Z5 format?

Re: removing leading zeros function

Reeza — Mon, 14 May 2018 19:27:52 GMT

Then you should use SUBSTR instead.
And COMPRESS() wouldn't work in general because it would remove all 0's, not just leading zeroes.

Re: removing leading zeros function

novinosrin — Mon, 14 May 2018 19:27:58 GMT

Can you please make your question fully comprehensive to avoid going back and forth, so that we can give the code in full in one go?

Does all your data have leading zeros or only in some?

and other details please3

Re: removing leading zeros function

RENATA1 — Mon, 14 May 2018 19:37:26 GMT

I have an existing global variable Pol_nbr - it is 9 characters long (it is a string, not a number):

%LET POL_NBR = '000742196','000012345';

I need to use two databases to extract data under the POL_NBR; one database uses all nine characters, the other uses six characters (without the first leading zeros).

I'm trying to find a function that converts POL_NBR into POL_NBR2 that would remove the three leading zeros and keep the remaining numbers unchanged.

Re: removing leading zeros function

Tom — Mon, 14 May 2018 19:40:32 GMT

@RENATA1 wrote:

I have an existing global variable Pol_nbr - it is 9 characters long (it is a string, not a number):

%LET POL_NBR = '000742196','000012345';

I need to use two databases to extract data under the POL_NBR; one database uses all nine characters, the other uses six characters (without the first leading zeros).

I'm trying to find a function that converts POL_NBR into POL_NBR2 that would remove the three leading zeros and keep the remaining numbers unchanged.

Are they actually numbers? If so then just convert them both to numbers and compare those..

select * 
from a
inner join b
on input(a.pol_nbr,32.) = input(b.pol_nbr2,32.)
;

Re: removing leading zeros function

Tom — Mon, 14 May 2018 19:45:50 GMT

I have an existing global variable Pol_nbr - it is 9 characters long (it is a string, not a number):

%LET POL_NBR = '000742196','000012345';

Actually you don't. You have a macro variable that is 23 characters long.

How did you create the macro variable?

If you used SAS code to make it from the underlying data then change the process that makes it.

select distinct input(pol_nbr,32.)
  into :number_list separate by ' '
  from table1
;
select * from table2
  where input(pol_nbr2,32.) in (&number_list)
;

Re: removing leading zeros function

Astounding — Mon, 14 May 2018 20:19:39 GMT

You probably don't need to convert them. As macro variables, these strings would only be valuable for a comparison, such as:

where policy_number in (&POL_NBR);

where policy_number in (&POL_NBR2);

If that is really the way you intend to use them, you can leave the macro variables alone and convert the "other side" of the comparison:

where put(input(policy_number, 9.), z9.) in (&POL_NBR);

where put(input(policy_number, 9.), z6.) in (&POL_NBR2);

If you have some other use for these macro variables, show us what you intend to do with them.

Re: removing leading zeros function

Patrick — Tue, 15 May 2018 00:05:05 GMT

@RENATA1 wrote:

I have an existing global variable Pol_nbr - it is 9 characters long (it is a string, not a number):

%LET POL_NBR = '000742196','000012345';

I need to use two databases to extract data under the POL_NBR; one database uses all nine characters, the other uses six characters (without the first leading zeros).

I'm trying to find a function that converts POL_NBR into POL_NBR2 that would remove the three leading zeros and keep the remaining numbers unchanged.

@RENATA1

The following code should do the job.

%LET POL_NBR = '000742196','000012345';
%let pol_nbr2=%sysfunc(prxchange(s/\b0{3}//oi,-1,%nrbquote(&pol_nbr)));
%let pol_nbr2=%unquote(&pol_nbr2);
%put pol_nbr2=&pol_nbr2;

Re: removing leading zeros function

RENATA1 — Tue, 15 May 2018 11:43:55 GMT

Thank you soo much for all the recommendations. Patrick's code solved my problem! Thank you!!