Solved
Occasional Contributor
Posts: 9

## Rakesh

Accepted Solutions
Solution
‎10-31-2016 12:42 PM
Super User
Posts: 10,788

[ 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.

All Replies
Solution
‎10-31-2016 12:42 PM
Super User
Posts: 10,788

[ 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

Hi ksharp,

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

Super Contributor
Posts: 464

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

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.

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

Super Contributor
Posts: 464

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

New Contributor
Posts: 4

data test;

x='0001451';

run;

data t1;

set test;

format y z7.;

y=x*1;

run;

Super User
Posts: 10,788

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

New Contributor
Posts: 4

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.

Posts: 4,743

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

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

Super Contributor
Posts: 644

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

Sry.. I need to do it in TOAD.

Super User
Posts: 8,129

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