topic Re: Adding leading Zeros to Numeric Variable in SAS Programming

Adding leading Zeros to Numeric Variable

Reid — Wed, 10 Oct 2012 04:35:12 GMT

Hi All,

Hers is the situation, where need to leading to the numeric variable ;

Example : ( data is in oracle sever )

cust.id ( Numeric )

---------------

374747830939

and Need to convert

cust.id ( Numeric )

---------------

000374747830939

Thanks in advance ,

Rakesh

Re: Adding leading Zeros to Numeric Variable

Ksharp — Tue, 03 Dec 2019 21:12:06 GMT

Easy. This converts the numeric to a formatted character value with leading zeros.

data x;
 a=374747830939;
 b=put(a,z15.);
 format a z15.;
run;

Ksharp

From @RichardinOz:

If joining two tables with a 'key' variable and the key varible in one table is numeric and in another it's character.

You can join 2 tables in SAS using keys as you describe quite easily. If table A has numeric key and B has char values then use the INPUT function to convert the char to a number for comparison.

Proc SQL ;
     Create table want as
          Select A.---
               ,    B.---
          From A A
               ,    B B
          Where a.key1 = input(B.key2, Best32.)
     ;
Quit ;

If you want to do the join in Oracle you can use Tom's code to achieve the same result, using a character comparison.

Re: Adding leading Zeros to Numeric Variable

Patrick — Wed, 10 Oct 2012 09:59:14 GMT

Ksharp showed you how to convert a numeric variable in SAS to a string with leading zeros stored in a character variable. Is this what you need?

...or is this something which needs to be done in Oracle (eg. to join with a Varchar having such leading zeros) ?

...or do you need a numeric variable in SAS which prints with leading zeros?

Re: Adding leading Zeros to Numeric Variable

Reid — Wed, 10 Oct 2012 14:05:42 GMT

Hi ksharp,

thanks for replay , I need the "b" varible be numeric. your code gives in char varible after chnaging it.

Re: Adding leading Zeros to Numeric Variable

Reid — Wed, 10 Oct 2012 14:12:17 GMT

@ patrick,

Actually , I am joing two tables with 'key' varible. The key varible in one table it is numeric and in another it's charecter. So i need to convert char to numeric, During the converiosn i shoud not remove leading zeros. So i need help in adding leading zero's.

Thanks for your hlep,

Re: Adding leading Zeros to Numeric Variable

Reid — Wed, 10 Oct 2012 14:13:17 GMT

Sry.. I need to do it in TOAD.

Re: Adding leading Zeros to Numeric Variable

Tom — Wed, 10 Oct 2012 16:05:24 GMT

It is easier to convert the character variable into numbers. Only issue would be if you had values in the character variable that are different, but convert to the same number. ('012' and '12' for example) But in that case you will have problems with your merge anyway.

* Convert character variable to numeric ;

select * from a,b where a.id = input(b.id,15.)

* Convert numeric variable to character ;

select * from a,b where put(a.id,z15.) = b.id ;

If you are using TOAD then you need to know the SQL syntax for the database you are pulling from to be able to write the code to convert. What type of database are you pulling the data from? Oracle, SQL Server, Teradata, ....

Why are you using TOAD? Why not just query the database using SAS?

Re: Adding leading Zeros to Numeric Variable

Tal — Wed, 10 Oct 2012 20:48:20 GMT

is it possible to add leading "0"s to a numeric variable in SAS and still have it as numeric?

Re: Adding leading Zeros to Numeric Variable

ballardw — Wed, 10 Oct 2012 21:31:10 GMT

The interal storage of numbers doesn't change but for any time you want to SEE leading zeroes use the Zw.d format.

Re: Adding leading Zeros to Numeric Variable

Tom — Wed, 10 Oct 2012 23:00:42 GMT

You can add as many leading zeros to a number as you want and it will not change the value.

0 x 10**100 is still 0.

Re: Adding leading Zeros to Numeric Variable

Reid — Thu, 11 Oct 2012 04:35:03 GMT

I am using oracle . I change my mind, I will use SAS to accesses the oracle data. But still that conversion bugs me .( Char to Numeric with leading zero ) ....hopefully I will figure out something..

Re: Adding leading Zeros to Numeric Variable

Reid — Thu, 11 Oct 2012 04:36:19 GMT

i think so , i will defiantly post it. If i come up with come up some thing...

Re: Adding leading Zeros to Numeric Variable

Ksharp — Thu, 11 Oct 2012 06:15:47 GMT

So my A is still numeric variable.

but its internal value is 374747830939 , it looks like 000374747830939 .

Re: Adding leading Zeros to Numeric Variable

Patrick — Thu, 11 Oct 2012 12:00:27 GMT

Do the join where your data lives - so do it in Oracle. I was thinking that it shouldn't be too hard to convert character to numeric (or the other way round) in Oracle using either TO_NUMBER() or TO_CHARACTER.

Being an iregular Oracle user I Googled the doc to post the correct syntax - and then look what I found under Datatype Comparison Rules

"When comparing a character value with a numeric value, Oracle converts the character data to a numeric value."

Re: Adding leading Zeros to Numeric Variable

Tal — Thu, 11 Oct 2012 17:29:33 GMT

but Reid,

why cant you add the "0"s after the conversion?

I dont understand.I dont think you can convert char to num and still be able to manage keeping the leading "0"s without adding them afterwards

Re: Adding leading Zeros to Numeric Variable

Reid — Thu, 11 Oct 2012 20:35:51 GMT

Could you tell me how add after converting Char to Numeric .( in oralce )

I was readiing paper (see below title ), oracel won't save numeric variable value with leading zero.

Let me know your thoughts...

Using SAS® with Oracle®: Writing efficient and accurate SQL

Re: Adding leading Zeros to Numeric Variable

Tal — Thu, 11 Oct 2012 21:47:34 GMT

sorry,i was talking SAS not Oracle.Dont know how you could do it in Oracle

Re: Adding leading Zeros to Numeric Variable

Tom — Thu, 11 Oct 2012 22:07:31 GMT

You cannot add leading zeros to a number. It just does not make sense either mathematical or in computer science.

What is your actual problem? Are you having trouble querying the data? Or matching records between two tables (datasets) ?

Can you show the example data?

With SAS you can add a format to display a number with leading zeros. You can even have the format permanently attached to the variable in the definition of the dataset so that the default for displaying the number will include the leading zeros. I do not think you can do the same in Oracle. You can use a function call to convert a number to a character string and specify that leading zeros are printed. Is that what you want to know how to do? To do that in Oracle use to TO_CHAR function with a format.

Example:

to_char(12.349,'099999.99') returns "000012.35"

Re: Adding leading Zeros to Numeric Variable

kellysmith — Fri, 04 Jan 2013 15:31:46 GMT

data test;

x='0001451';

run;

data t1;

set test;

format y z7.;

y=x*1;

run;

Re: Adding leading Zeros to Numeric Variable

kellysmith — Fri, 04 Jan 2013 15:33:21 GMT

data test;

x='0001451';

run;

data t1;

set test;

format y z7.;

y=x*1;

run;

After running your code, this code will keep the leading zeros after converting the varible from char to numeric.