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

[Ksharp]

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 .

[HULK]

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

[Ksharp]

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 ?

[HULK]

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

[yabwon]

Hi,

you have:

CREATE TABLE TEST_TBL5(T1 NUMBER ,T2 NUMBER )

maybe according to:

 https://documentation.sas.com/?docsetId=acreldb&docsetTarget=p06jk0u30uhuj5n18fqw9sxr25lk.htm&docset...

as in the "Default SAS Formats for Oracle NUMBER Data Type" table the default format is:

"NUMBER p, s unspecified BEST22. SAS selects format"

For your 18 digits:

data _null_;
  x=111111111111111111;
  put x= BEST22.;
run;

log shows:

1    data _null_;
2    x=111111111111111111;
3    put x= BEST22.;
4    run;

x=111111111111111120

Bart

[HULK]

Yes, this is true for 18-digit numbers due to the limitation in the SAS for numerical variables of 8 bytes.
But in my example, 16-digit numbers and they are less than the value set as the limit (9,007,199,254,740,992 ).

[yabwon]

As far as I can count, from your example: `T1 = 111111111111111111 and T2 = 111111111111111112`, they both are 18-digits 😉

Bart

[HULK]

Sorry, my bad - make error in comment. Throughout this task, tests were performed with 16-digit numbers. 

[Tom]

That is 18 digits, not 16.

[HULK]

Sorry, but I don’t understand what you mean. In the examples I indicated 16-digit numbers and the question was also about them. Not about 18-digit numbers - because they exceed the established limit by size.

[SASKiwi]

@HULK  - Try this test program:

data test;
T1 = 1111111111111111;
T2 = put(T1, 16.);
format T1 16.;
put _all_;
run;

PROC EXPORT Data  = Test  
            FILE = "Test.xlsx"
            DBMS = EXCEL 
            REPLACE
            ;
  sheet = "Test";
RUN;

It has exactly the same problem you have with Oracle - the 16th digit becomes a 0. My take on this is that when SAS interfaces with other products then it loses precision on 16 digit numbers, regardless of whether SAS internally can hold that number accurately.

The only reliable solution is to translate the 16 digits into character when storing in SAS then translating it back to numeric when writing out.   

[TomKari]

@SASKiwi Excellent test! I think you've clearly demonstrated that something is going wrong in SAS.

@HULK I suggest you open a case with SAS technical support over this. I wonder if it might come as a surprise to them!

Tom

[TomKari]

@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

[merjaq]

Hi all! I'm having exactly the same problem as @HULK in the original post. I have a table in Oracle database with ID variable having some 16-digit-values . Reading them to SAS server works fine and all the values are displayed correctly on SAS server but if I write them to Oracle (or Excel) again, 16. digit converts to 0. I'm confused why this is happening since both environments "understand" the original & correct values. I have a workaround but I'm still wondering if there's something one could do to prevent this error.

[ballardw]

Please start new threads and then reference an older on like this with a link.

Note that in general ID variables should never actually be numeric. If you don't do arithmetic with them then they aren't actually numbers and stored as character values would not have any such issue.

Also it is a good idea to describe just how you "write to Oracle or Excel". Especially Excel as Excel will convert character values to numeric with amazing frequency (and generate problematic values all too often). One thing to always consider with anything resembling "write" is the FORMAT that is used by SAS for display of the values as that often gets used in the write process and a misunderstanding of how a specific format works may be involved.