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.
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.
Hi ksharp,
thanks for replay , I need the "b" varible be numeric. your code gives in char varible after chnaging it.
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
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
sorry,i was talking SAS not Oracle.Dont know how you could do it in Oracle
data test;
x='0001451';
run;
data t1;
set test;
format y z7.;
y=x*1;
run;
Then 'a' variable is what you are looking for ?
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.
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?
@ 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,
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
Sry.. I need to do it in TOAD.
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?
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..
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.