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!
Hi,
Just try this
to_char(account_number,'9999999999999999999')Sorry i cannot confirm if its correct.
Hi,
Just try this
to_char(account_number,'9999999999999999999')Sorry i cannot confirm if its correct.
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.
How about something like this
cast(account_number,varchar2(30))
does this work..
I can't test this but have you tried the TM9 format with to_char?
Hmmm...it seemed it worked again with the 9999999 solution but adding more. Very strange.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.