After loading a 16-digit numerical variable into Oracle, the last character is replaced with 0.

[HULK]

Hi!

Met the following problem.

There is a dataset with numerical variables consisting of 16 digits.

Example:

data test_tbl;
format t1 t2 t3 t4 t5 t6 t7 t8 t9 best20.;
t1 = 1111111111111111;
t2 = 2222222222222222;
t3 = 3333333333333333;
t4 = 4444444444444444;
t5 = 5555555555555555;
t6 = 6666666666666666;
t7 = 7777777777777777;
t8 = 8888888888888888;
t9 = 9999999999999999;
run;

After running this part of the code, the dataset is as follows:

The screenshot shows that all 16-digit numbers are displayed correctly, except for the last - 9999999999999999. It is rounded.

Further, we are trying to upload this table to the Oracle database:

proc sql;
create table oracle_lib.test_tbl
AS SELECT * FROM work.test_tbl;
quit;

In the database, this table is as follows

Now we see that all 16-digit numbers are rounded.

We need the correct display of 16-digit numbers in the database. The option of converting to a character format does not suit us.

I read on the forum that other users had problems with rounding large numbers, but they had a problem, if the number consists of more than 16 digits. In this particular case, all numbers do not exceed the 16-digit format.

Any ideas why this is happening? Is there any way to upload such numbers into the database in the correct format?

[Ksharp]

The max length for number in SAS is 15 , therefore transfer it into oracle by a string, and let oracle DM translate it back .

[HULK]

Is there a documented statement that the length of a number in cac is 15 digits?

By searching the sas documentation, I found information that sas can work with a maximum of 8 byte numbers (the maximum number is 9,007,199,254,740,992). This is just the same confirmed by the first case from the description of the problem (all numbers are displayed correctly except 9999999999999999).

Problems begin to appear only when uploading to Oracle Database.

[yabwon]

One question, what is the source of the test_tbl table? Is it a text file by chance?

Maybe you could do it like this:

1) Load text file with 16 digit numbers data into SAS dataset as text strings.

2) use Proc DS2 to convert text to bigint(up to 19 digits) [https://documentation.sas.com/api/docsets/ds2pg/9.4/content/ds2pg.pdf?locale=en]

3) upload oracle from the Proc DS2

Bart

[HULK]

Unfortunately, the source of the table is numerical data, not text.
This upload to the database is an integral part of a larger and more complex process.

In the description of the problem, I just gave an example of how to reproduce this problem.

[Tom]

I suspect that issue might be on Oracle side with converting the floating point value gotten from SAS into the DECIMAL(16) number. 

Can you provide more details on what you actually did?  You might need to just convert the SAS numeric to string and upload that and then convert back to DECIMAL(16) in the remote database.

You might want to open a support ticket with SAS so they can give more detailed support.

[HULK]

Thank you, but as I wrote earlier, the option of downloading a text format is not currently considered.

Reproducing Steps:

1. Create a dataset with 16-digit numeric variables.
2. Upload it to Oracle through "Create table ... as select * from dataset_from_step_N1 " or through the sqlldr utility - it does not matter.
3. Make sure that in the database the last digit of the number is rounded.

If you create a dataset in SAS based on what was done in step one, through "Create table ... as select * from dataset_from_step_N1", you can see that numbers less than 9,007,199,254,740,992 (as stated in the documentation) are not rounded.

[Tom]

---

@HULK wrote:

Thank you, but as I wrote earlier, the option of downloading a text format is not currently considered.

Reproducing Steps:

1. Create a dataset with 16-digit numeric variables.
2. Upload it to Oracle through "Create table ... as select * from dataset_from_step_N1 " or through the sqlldr utility - it does not matter.
3. Make sure that in the database the last digit of the number is rounded.

If you create a dataset in SAS based on what was done in step one, through "Create table ... as select * from dataset_from_step_N1", you can see that numbers less than 9,007,199,254,740,992 (as stated in the documentation) are not rounded.

---

I am now lost to understand what you are testing. You seem to be saying that it does work correctly.

Not sure what step 1 above is.  SAS only has one type of numbers, floating point numbers.   But SAS/Access will normally attempt to define data type in the remote database based on the format attached to the number.  What format did you attach to the numbers?  I am not sure what format SAS/Access will use when transferring numbers that do not have a format specified.  In most other places SAS will default to using BEST12. format.  If that happens then you might lose the last few decimal digits of your larger numbers.  Also if you specify the format as 16. then perhaps SAS is worried about negative numbers and only used 15 of the digits when sending it to Oracle?  What if you use 17. as the the attached SAS format instead?

[HULK]

Data uploading works incorrectly according to the following algorithm:

SAS  =>> Oracle DB (round last digit in 16-digit numbers)

Now I tried to upload data in format 17 as follows:

data work.test_tbl;
format t1 t2 t3 t4 t5 t6 t7 t8 t9 17.;
t1 = 1111111111111111;
t2 = 2222222222222222;
t3 = 3333333333333333;
t4 = 4444444444444444;
t5 = 5555555555555555;
t6 = 6666666666666666;
t7 = 7777777777777777;
t8 = 8888888888888888;
t9 = 9999999999999999;
run;


proc sql;
create table oracle_lib.test_tbl
AS SELECT * FROM work.test_tbl;
quit;

The result is the same:

although the format in the database table corresponds to what was specified in SAS.

[Tom]

Talk to SAS support. http://sas.com/support 

If you want to keep seeing if you can debug it try turning on the logging of the SQL commands the SAS/Access uses to create the table and send the data.  Look at the SASTRACE option.  Perhaps something like this:

options sastrace=',,,ds' sastraceloc=saslog ;

[Tom]

What happens if you just tell Oracle to make it a floating point number like it was in SAS?  

Something like this.

data test_tbl;
  input testvar ;
  format testvar F16.;
cards;
111111111111111
2222222222222222
3333333333333333
4444444444444444
5555555555555555
6666666666666666
7777777777777777
8888888888888888
9999999999999999
;
proc sql;
create table oracle_lib.test_tbl(dbtype=(testvar='float'))
AS SELECT testvar FROM test_tbl;
quit;
proc compare data=test_tbl compare= oracle_lib.test_tbl ;
run;

[TomKari]

Hi, @HULK 

From this post, it looks like there's a very important point being missed. As soon as you execute the code:

data work.test_tbl;
format t1 t2 t3 t4 t5 t6 t7 t8 t9 17.;
t1 = 1111111111111111;
t2 = 2222222222222222;
t3 = 3333333333333333;
t4 = 4444444444444444;
t5 = 5555555555555555;
t6 = 6666666666666666;
t7 = 7777777777777777;
t8 = 8888888888888888;
t9 = 9999999999999999;
run;

the value for t9 as stored by SAS is incorrect. There's no issues of formats, or anything else. You simply can't reliably store 16 digit numbers like 9999999999999999  accurately in SAS as a numeric data type. It's not SAS, it's the limit of 8 byte floating point. Some of them will be stored correctly, most won't. The instant the 

t9 = 9999999999999999;

statement is executed, the value stored by SAS is different, and can never be recovered.

Here's the relevant document section:

https://documentation.sas.com/?docsetId=pgmcli&docsetTarget=p12zsdbylnn6c2n1i48z7djr6uzo.htm&docsetV... 

Tom

[Ksharp]

The following code return the exact integer the sas could reach biggest.

data _null_;
max=constant('exactint');
put max= best32.;
run;

[TomKari]

@HULK  or alternately, if you run this code you'll see once the biggest number is created, the addition of 1 is just discarded, as there aren't enough significant digits in the field (thanks for the constant function, @Ksharp! I didn't know about it).

data _null_;
	bignum=constant('exactint');
	bignum = bignum - 4;

	do i = 1 to 10;
		bignum = bignum + 1;
		put bignum= best32.;
	end;
run;

Tom

[HULK]

My question was not about the largest number sas can work with.

The question was why 16-digit numbers (for example, var = 1111111111111111, and it is less than the maximum) when loading into Oracle database, rounds the last digit in the number.
If you have a test database, you can try to reproduce this. In the database, this number will be displayed as 111111111111111110.