BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gkn99
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
NN
Quartz | Level 8 NN
Quartz | Level 8

Hi,

Just try this

to_char(account_number,'9999999999999999999')

Sorry i cannot confirm if its correct.

View solution in original post

5 REPLIES 5
NN
Quartz | Level 8 NN
Quartz | Level 8

Hi,

Just try this

to_char(account_number,'9999999999999999999')

Sorry i cannot confirm if its correct.

gkn99
Calcite | Level 5

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. 

NN
Quartz | Level 8 NN
Quartz | Level 8

How about something like this

cast(account_number,varchar2(30))

does this work..

art297
Opal | Level 21

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

gkn99
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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