Adding leading Zeros to Numeric Variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Adding leading Zeros to Numeric Variable

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


Accepted Solutions
Solution
‎10-31-2016 12:42 PM
Grand Advisor
Posts: 9,571

Re: Adding leading Zeros to Numeric Variable

[ Edited ]

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.

View solution in original post


All Replies
Solution
‎10-31-2016 12:42 PM
Grand Advisor
Posts: 9,571

Re: Adding leading Zeros to Numeric Variable

[ Edited ]

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.

Occasional Contributor
Posts: 9

Re: Adding leading Zeros to Numeric Variable

Hi ksharp,

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


Super Contributor
Super Contributor
Posts: 434

Re: Adding leading Zeros to Numeric Variable

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

Occasional Contributor
Posts: 9

Re: Adding leading Zeros to Numeric Variable


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

Super Contributor
Super Contributor
Posts: 434

Re: Adding leading Zeros to Numeric Variable

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

New Contributor
Posts: 4

Re: Adding leading Zeros to Numeric Variable

data test;

x='0001451';

run;

data t1;

set test;

format y z7.;

y=x*1;

run;

Grand Advisor
Posts: 9,571

Re: Adding leading Zeros to Numeric Variable

Then 'a' variable is what you are looking for ?

New Contributor
Posts: 4

Re: Adding leading Zeros to Numeric Variable

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.

Respected Advisor
Posts: 3,825

Re: Adding leading Zeros to Numeric Variable

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?

Occasional Contributor
Posts: 9

Re: Adding leading Zeros to Numeric Variable

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

Super Contributor
Posts: 644

Re: Adding leading Zeros to Numeric Variable

Actually , I am joing two tables with 'key' varible. The key varible in one table it is numeric and in another it's charecter.


You can join 2 tables in SAS using keys as you describe quite easily.  If table A has numeric kesy and B has text values then


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 achive the same result, using a character comparison.


Richard


Occasional Contributor
Posts: 9

Re: Adding leading Zeros to Numeric Variable

Sry.. I need to do it in TOAD.

Super User
Super User
Posts: 6,318

Re: Adding leading Zeros to Numeric Variable

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?

Occasional Contributor
Posts: 9

Re: Adding leading Zeros to Numeric Variable

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.. Smiley Happy

☑ This topic is SOLVED.

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

Discussion stats
  • 21 replies
  • 99100 views
  • 5 likes
  • 8 in conversation