Desktop productivity for business analysts and programmers

removing leading zeros function

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

removing leading zeros function

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


Accepted Solutions
Solution
‎05-15-2018 07:36 AM
Respected Advisor
Posts: 4,779

Re: removing leading zeros function


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

View solution in original post


All Replies
Super User
Posts: 23,959

Re: removing leading zeros function

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


 

Super User
Posts: 2,047

Re: removing leading zeros function

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

%let POL_NBR=%sysfunc(compress(%bquote(&POL_NBR),0));
%put POL_NBR=&POL_NBR;
New Contributor
Posts: 4

Re: removing leading zeros function

Posted in reply to novinosrin

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?

Super User
Super User
Posts: 8,264

Re: removing leading zeros function


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

Super User
Posts: 23,959

Re: removing leading zeros function

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

Re: removing leading zeros function

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

New Contributor
Posts: 4

Re: removing leading zeros function

Posted in reply to novinosrin

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. 

 

 

Super User
Super User
Posts: 8,264

Re: removing leading zeros function


@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.)
;
Super User
Super User
Posts: 8,264

Re: removing leading zeros function

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)
;
Solution
‎05-15-2018 07:36 AM
Respected Advisor
Posts: 4,779

Re: removing leading zeros function


@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;
New Contributor
Posts: 4

Re: removing leading zeros function

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

Super User
Super User
Posts: 8,264

Re: removing leading zeros function

[ Edited ]

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

 

Super User
Posts: 6,905

Re: removing leading zeros function

[ Edited ]

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.

☑ This topic is solved.

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

Discussion stats
  • 13 replies
  • 305 views
  • 3 likes
  • 6 in conversation