BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RENATA1
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

13 REPLIES 13
Reeza
Super User

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


 

novinosrin
Tourmaline | Level 20
%LET POL_NBR = '000742196','00012345';

%let POL_NBR=%sysfunc(compress(%bquote(&POL_NBR),0));
%put POL_NBR=&POL_NBR;
RENATA1
Fluorite | Level 6

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?

Tom
Super User Tom
Super User

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

Reeza
Super User
Then you should use SUBSTR instead.
And COMPRESS() wouldn't work in general because it would remove all 0's, not just leading zeroes.
novinosrin
Tourmaline | Level 20

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

RENATA1
Fluorite | Level 6

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. 

 

 

Tom
Super User Tom
Super User

@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.)
;
Tom
Super User Tom
Super User

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)
;
Patrick
Opal | Level 21

@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;
RENATA1
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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

 

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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