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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.