DATA Step, Macro, Functions and more

Converting Oracle Integer to SAS Numeric

Reply
Frequent Contributor
Posts: 83

Converting Oracle Integer to SAS Numeric

Hi ,

While reading a table from Oracle which has a column containing an Oracle Integer value of 17 digits from sas and converting to a dataset , is displayed in SAS as Exponentinal no.

ex REGNO(ORACLE) 12345678912345445

VAR(SAS)
1.26E16

However if I convert it again into numeric using Input function while reading , the value would be rounded 12345678912345450 .

Please could someone suggest an alternative to display the full digits .

Message was edited by: tommy81

Message was edited by: tommy81 Message was edited by: tommy81
Super User
Posts: 9,676

Re: Converting Oracle Integer to SAS Numeric

Unfortunately,I guess your SAS only has exact int about 16 digit.You can test it by yourself
with the following my code,that mean you can not use it to mathematical ,But you can
display it by transforming it into character type or make length longer.(only suited for 17 digit)
p.s 8 is the max bytes for numeric data in my computer. So only can exact to 16 digit.


[pre]
data _null_;
exactint=constant('exactint',8);
put 'exactint=' exactint ;
length test 8;
input test best20.;
put 'test=' test best20.;
char_test=put(test,best20.);
put 'char_test=' char_test;
stop;
cards;
12345678912345678
;
run;

[/pre]


Ksharp
Trusted Advisor
Posts: 1,300

Re: Converting Oracle Integer to SAS Numeric

You could convert the oracle integer to character during pull.

proc sql;
connect to oracle(user=blah orapw=blah path=blah connection=global);

create table blah as select * from connection to oracle
( select to_char(integer,9999999999999999) as char from table );

disconnect fro oracle;
quit;
PROC Star
Posts: 1,561

Converting Oracle Integer to SAS Numeric

It is all explained here:

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000695157.htm

and

http://www.sascommunity.org/wiki/Numeric_representation_issues_in_SAS

The larger integer sas can accurately represent with 8 bytes is 9,007,199,254,740,990 under Windows and 72,057,594,037,927,900 on mainframes.

Your number 12,345,678,912,345,445 is too large for the Windows platform.

Ask a Question
Discussion stats
  • 3 replies
  • 892 views
  • 0 likes
  • 4 in conversation