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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

 

 

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

21 REPLIES 21
Ksharp
Super User

 

 

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.

Reid
Fluorite | Level 6

Hi ksharp,

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


Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

Reid
Fluorite | Level 6


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

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

kellysmith
Calcite | Level 5

data test;

x='0001451';

run;

data t1;

set test;

format y z7.;

y=x*1;

run;

Ksharp
Super User

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

kellysmith
Calcite | Level 5

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.

Patrick
Opal | Level 21

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?

Reid
Fluorite | Level 6

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

RichardinOz
Quartz | Level 8

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


Reid
Fluorite | Level 6

Sry.. I need to do it in TOAD.

Tom
Super User Tom
Super User

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?

Reid
Fluorite | Level 6

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 21 replies
  • 217070 views
  • 10 likes
  • 8 in conversation