topic Converting Long Numerics to Character from Oracle Database in SAS Procedures

Converting Long Numerics to Character from Oracle Database

gkn99 — Mon, 22 Aug 2011 15:19:47 GMT

I was wondering if you could help me. I am trying to pull data from

the Oracle tables the SAS access passthrough. The account numbers are

stored in the oracle databases as numeric (19 digits) and so if you

pull it down as normal (or using a libname), the last few digits lose

precision.

I tried the following and while the customer_id is character with 19
digits, it also loses precision (seems that the last 2-3 numbers are
garbage/random).

proc sql;
connect to oracle as oradb (user=xxxxx password=’xxxxxxx'
path='dbname' );

          create table dat.test as
            select * from connection to oradb
            (
              SELECT to_char(account_number) as customer_id
                     ,model_score
                     from FLOW_RESULT where rownum<15
            );

disconnect from oradb;
quit;

Any help woudl be much appreciated!

Converting Long Numerics to Character from Oracle Database

NN — Mon, 22 Aug 2011 16:00:10 GMT

Hi,

Just try this

to_char(account_number,'9999999999999999999')

Sorry i cannot confirm if its correct.

Converting Long Numerics to Character from Oracle Database

gkn99 — Mon, 22 Aug 2011 16:04:25 GMT

I have tried this. I also tried making it longer than 19 (more 9's) and I still lose precision.

In Teradata, cast works when you set the mode=teradata. But I don't know how to do this in Oracle.

Converting Long Numerics to Character from Oracle Database

NN — Mon, 22 Aug 2011 17:09:54 GMT

How about something like this

cast(account_number,varchar2(30))

does this work..

Converting Long Numerics to Character from Oracle Database

art297 — Mon, 22 Aug 2011 17:22:03 GMT

I can't test this but have you tried the TM9 format with to_char?

Converting Long Numerics to Character from Oracle Database

gkn99 — Mon, 22 Aug 2011 17:28:23 GMT

Hmmm...it seemed it worked again with the 9999999 solution but adding more. Very strange.