Help using Base SAS procedures

Converting Long Numerics to Character from Oracle Database

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Converting Long Numerics to Character from Oracle Database

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!


Accepted Solutions
Solution
‎08-22-2011 12:00 PM
Regular Contributor
Regular Contributor
Posts: 166

Converting Long Numerics to Character from Oracle Database

Hi,

Just try this

to_char(account_number,'9999999999999999999')

Sorry i cannot confirm if its correct.

View solution in original post


All Replies
Solution
‎08-22-2011 12:00 PM
Regular Contributor
Regular Contributor
Posts: 166

Converting Long Numerics to Character from Oracle Database

Hi,

Just try this

to_char(account_number,'9999999999999999999')

Sorry i cannot confirm if its correct.

New Contributor
Posts: 3

Converting Long Numerics to Character from Oracle Database

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. 

Regular Contributor
Regular Contributor
Posts: 166

Converting Long Numerics to Character from Oracle Database

How about something like this

cast(account_number,varchar2(30))

does this work..

PROC Star
Posts: 7,363

Converting Long Numerics to Character from Oracle Database

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

New Contributor
Posts: 3

Converting Long Numerics to Character from Oracle Database

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 157 views
  • 0 likes
  • 3 in conversation