topic Re: After loading a 16-digit numerical variable into Oracle, the last character is replaced with 0. in SAS Enterprise Guide

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

HULK — Fri, 05 Jun 2020 12:04:55 GMT

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?

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

Ksharp — Fri, 05 Jun 2020 12:20:35 GMT

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

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

HULK — Fri, 05 Jun 2020 12:53:54 GMT

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.

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

yabwon — Fri, 05 Jun 2020 13:25:04 GMT

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

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

HULK — Fri, 05 Jun 2020 13:30:15 GMT

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.

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

Tom — Fri, 05 Jun 2020 14:26:26 GMT

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.

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

HULK — Fri, 05 Jun 2020 15:29:01 GMT

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.

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

Tom — Fri, 05 Jun 2020 15:47:39 GMT

@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?

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

HULK — Fri, 05 Jun 2020 16:03:33 GMT

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.

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

Tom — Fri, 05 Jun 2020 19:20:00 GMT

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 ;

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

Tom — Fri, 05 Jun 2020 19:26:45 GMT

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;

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

TomKari — Fri, 05 Jun 2020 22:37:52 GMT

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&docsetVersion=1.0&locale=en

Tom

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

Ksharp — Sat, 06 Jun 2020 11:37:31 GMT

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

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

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

TomKari — Sat, 06 Jun 2020 18:15:21 GMT

@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

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

HULK — Sun, 07 Jun 2020 09:48:40 GMT

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.

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

Ksharp — Sun, 07 Jun 2020 11:07:05 GMT

Can you change the variable type in oracle into Integer ,like
T1 INT(17)
T2 BIGINT(17)

proc sql;
connect to oralce(...)
execute( create table oracle.want ( T1 BIGINT(17), T2 BIGINT(17), ...... ) )
by oracle;
quit;
proc append base=oracle.want data=sas.have force;run;

Or Change the variable format at oracle side , see if the format affect it .

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

TomKari — Sun, 07 Jun 2020 15:27:58 GMT

@HULK My apologies, I just skimmed through the preceding messages, and missed this very important part. The problem of not being able to process numbers in SAS that are larger than the physical limit is a common one, so I just latched on to that. To me, it looks like your example should work, so something isn't working correctly. Unfortunately I don't have access to any Oracle facilities, so I'm afraid I can't contribute further.

Good luck, and please post back when you find out what the explanation is. I find this problem very interesting!

Tom

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

HULK — Sun, 07 Jun 2020 16:40:15 GMT

Unfortunately, this option did not help. The result is the same.

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

Ksharp — Mon, 08 Jun 2020 11:05:29 GMT

As Tom said,Could you use
options sastrace=',,,ds' sastraceloc=saslog ;

see what exact SQL(in LOG) sas pass into oralce and let oracle DB check this SQL ,why variable would lost its length in oracle ?

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

HULK — Mon, 08 Jun 2020 12:01:33 GMT

Here is an excerpt from the log. I tried to insert two columns T1 = 111111111111111111 and T2 = 111111111111111112

From suspicious moments, I saw only this in the log:

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

options sastrace=',,,ds' sastraceloc=saslog ;
27         proc sql;
28         create table test_ma.test_tbl5
29         as select * from work.test;
  0 1591617382 no_name 0 SQL (2) 
ORACLE_1: Prepared: on connection 0 1 1591617382 no_name 0 SQL (2) 
SELECT * FROM TEST_TBL5 2 1591617382 no_name 0 SQL (2) 
  3 1591617382 no_name 0 SQL (2) 
  4 1591617382 no_name 0 SQL (2) 
Summary Statistics for ORACLE are: 5 1591617382 no_name 0 SQL (2) 
Total SQL prepare seconds were:                     0.003498 6 1591617382 no_name 0 SQL (2) 
Total seconds used by the ORACLE ACCESS engine were     0.004462 7 1591617382 no_name 0 SQL (2) 
  8 1591617382 no_name 0 SQL (2) 
  9 1591617382 no_name 0 SQL (2) 
ORACLE_2: Prepared: on connection 1 10 1591617382 no_name 0 SQL (2) 
SELECT * FROM TEST_TBL5 11 1591617382 no_name 0 SQL (2) 
  12 1591617382 no_name 0 SQL (2) 
  13 1591617382 no_name 0 SQL (2) 
Summary Statistics for ORACLE are: 14 1591617382 no_name 0 SQL (2) 
Total SQL prepare seconds were:                     0.001377 15 1591617382 no_name 0 SQL (2) 
Total seconds used by the ORACLE ACCESS engine were     0.001772 16 1591617382 no_name 0 SQL (2) 
  17 1591617382 no_name 0 SQL (2) 
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
  18 1591617382 no_name 0 SQL (2) 
ORACLE_3: Executed: on connection 2 19 1591617382 no_name 0 SQL (2) 
CREATE TABLE TEST_TBL5(T1 NUMBER ,T2 NUMBER ) 20 1591617382 no_name 0 SQL (2) 
  21 1591617382 no_name 0 SQL (2) 
  22 1591617382 no_name 0 SQL (2) 
ORACLE_4: Prepared: on connection 2 23 1591617382 no_name 0 SQL (2) 
INSERT  INTO TEST_TBL5 (T1,T2) VALUES (:T1,:T2) 24 1591617382 no_name 0 SQL (2) 
  25 1591617382 no_name 0 SQL (2) 
  26 1591617382 no_name 0 SQL (2) 
2                                                          The SAS System                                 14:53 Monday, June 8, 2020

ORACLE_5: Executed: on connection 2 27 1591617382 no_name 0 SQL (2) 
INSERT statement  ORACLE_4 28 1591617382 no_name 0 SQL (2) 
  29 1591617382 no_name 0 SQL (2) 
ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* 30 1591617382 no_name 0 SQL (2) 
  31 1591617382 no_name 0 SQL (2) 
Summary Statistics for ORACLE are: 32 1591617382 no_name 0 SQL (2) 
Total SQL execution seconds were:                   0.047399 33 1591617382 no_name 0 SQL (2) 
Total SQL prepare seconds were:                     0.001399 34 1591617382 no_name 0 SQL (2) 
Total SQL row insert seconds were:                  0.016397 35 1591617382 no_name 0 SQL (2) 
Total seconds used by the ORACLE ACCESS engine were     0.068465 36 1591617382 no_name 0 SQL (2) 
  37 1591617382 no_name 0 SQL (2) 
NOTE: Table TEST_MA.TEST_TBL5 created, with 1 rows and 2 columns.

30         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.18 seconds
      cpu time            0.03 seconds