topic Converting Oracle Integer to SAS Numeric in SAS Programming

Converting Oracle Integer to SAS Numeric

tommy81 — Tue, 01 Mar 2011 08:05:06 GMT

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

Re: Converting Oracle Integer to SAS Numeric

Ksharp — Tue, 01 Mar 2011 09:34:04 GMT

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

Re: Converting Oracle Integer to SAS Numeric

FriedEgg — Thu, 03 Mar 2011 23:15:42 GMT

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;

Converting Oracle Integer to SAS Numeric

ChrisNZ — Wed, 10 Aug 2011 05:59:16 GMT

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.