Bulkload introducing rounding errors to datetime variables

[js5]

Dear community,

I have just discovered something very peculiar with SAS access to ODBC: for a reason yet unknown, saving datetime values in an Microsoft SQL database results in a one second shift if bulkload is enabled: 13AUG2018:16:35:00 will shift to 13AUG2018:16:34:59 if bulkload is used but remain unchanged if it is not. This is happening with all versions of ODBC driver (11, 13 and 17) except for the ancient one built into windows.

Is this a known issue? Thank you for your support in advance!

[JBailey]

Hi @js5

The situation you describe has historically been vendor (not SAS) defects in the ODBC driver and loader. You may want to call it a day and open a tech support track. That is probably the quickest way to get past this.

That being said...

I have tested this with SAS 9.5M5 and I am not seeing the behavior you describe (I may not have an ODBC driver that will show the issue). In order to help solve this we need to see the SAS code you are using to test this, and the interactions between SAS and the database. Pay special attention to the SASTRACE= option... 

Here is the SAS code I am submitting...

libname mymssql odbc dsn=mssql user=someuser password=somepassword;

data work.datetest;
   format x datetime22.3;
   input x datetime22.3;
cards;
13AUG2018:16:35:00
run;

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

proc append base=mymssql.datetest1 (bulkload=yes) data=work.datetest;
run;

proc append base=mymssql.datetest2 data=work.datetest;
run;

When I look at the resulting tables I see - 13AUG2018:16:35:00.000 - in both tables. 

In my SAS log I see that the CREATE TABLE statements (other than the table names )are the same for both tables. Pay special attention to the data types - I see DATETIME for both tables.

Good Luck,
Jeff

[js5]

Hi @JBailey,

Thank you for looking into this! The code I am using is nothing too extraordinary:

data _null_;
	length dataset $32;
	input dataset;
	call symputx(cats("dataset", _n_), dataset);
	call symputx("last_dataset", _n_);
	datalines;
        dataset_a
        dataset_b
        dataset_c
        dataset_d
        dataset_e
	;
run;

%macro transfer_to_sql;

	libname sqllib odbc prompt="Driver={ODBC Driver 13 for SQL Server};
		Server=xxxx;
		Database=yyyy;
		Trusted_Connection=yes;"
		bulkload=yes;

	proc sql;
		%do i = 1 %to &last_dataset;
			drop table sqllib.&&dataset&i;
			create table sqllib.&&dataset&i as 
				select * from saslib.&&dataset&i;
		%end;
	quit;

	libname sqllib clear;

%mend transfer_to_sql;

%transfer_to_sql

I was debugging this further and I also was not able to reproduce the problem with a simple dataset created with datalines. As I cannot share the complete dataset causing the issue, I need to distill it down to the smallest possible reproducer.

Using

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

I was able to see that no matter whether bulkload is enabled or not, the CREATE TABLE statements are the same in both cases. The affected columns are created as datetime2(0) datatype.

ETA1: this is very very strange. With my original data the issue can be reproduced with just one column and one row selected - effectively inserting just one value into the database, i.e.:

proc sql;
	%do i = 1 %to &last_dataset;
		drop table sqllib.&&dataset&i;
		create table sqllib.&&dataset&i as 
			select 'variable a'n
      			from saslib.&&dataset&i where 'variable b'n EQ 74;
	%end;
quit;

On the other hand, with the exact same value created with datalines, the issue can no longer be reproduced, even with the same dataset name, variable name and variable format.

What is even stranger, the issue can still be reproduced when going via a temporary table:

proc sql;
	%do i = 1 %to &last_dataset;
		create table t_&&dataset&i as 
			select 'variable a'n
      			from saslib.&&dataset&i where 'variable b'n EQ 74;			
		drop table sqllib.&&dataset&i;
		create table sqllib.&&dataset&i as 
			select * from t_&&dataset&i;
	%end;
quit;

ETA2: I believe I may have found the source of the issue. Intrigued by the fact that inserting a single value into the DB allows to reproduce the problem when selecting from original table but not when using datalines, I have compared both values in hex16. format. Interestingly enough, the hex16. value for 2018-04-11 22:35:00 when taken from my original data is 41DB67A144FFFFFE but when entered using datalines it is 41DB67A145000000 which is 0000000000000002 more.

The original data is imported from an Excel file using xlsx engine. Is this a bug, or just a limitation of xlsx importer?

ETA3: The issue can be reproduced using the attached file and the following code:

proc import datafile=odbc_datetime_precision_issue.xlsx
	out=sqllib.testcase(bulkload=yes)
	replace
	dbms=xlsx;
	getnames=no;

[JBailey]

Hi @js5

It sounds like there may be an issue with some of your data. It sounds like there may be a format (or informat) that is truncating some of the columns.

Your strategy about finding the observations which display the problem and comparing them to correct values is a strong one.

Best of luck,

Jeff

[FreelanceReinh]

Hi @js5,

Excel uses fractions of days for the time component of a datetime value. In your example, 22:35 h is expressed as

(22*3600+35*60)/86400=0.9409722222222...

and together with the date component (11 Apr 2018) the number is 43201.9409722222..., in theory with an infinite sequence of 2s. However, in practice the computer stores this number in a binary format with, of course, only a finite number of bits (binary digits). Using the IEEE floating-point format which is also used by SAS (under Windows) that binary number amounts to

1010100011000001.1111000011100011100011100011100011100

with 53 digits in total, where subsequent digits (01...) have been rounded off. Hence, in the decimal system this number is already slightly smaller than it should be:

43201.9409722222189884...

I unzipped your example .xlsx file and opened the relevant sheet1.xml file in Notepad. Excerpt:

<v>43201.940972222219</v>

Obviously, this is a rounded decimal representation of the value above.

Presumably, SAS reads this value and, after another decimal-to-binary conversion, converts it into a SAS datetime value (i.e., subtracts an offset of 21916 days and multiplies the result by 86400, but all this is done in the binary system). In the decimal sytem the result should be something like 1839105299.99999972..., in the internal hexadecimal representation: 41DB67A144FFFFFF, where the last "F" is rounded up from "ED...". Except for the last rounding (up vs. down), this is what you found using the HEX16. format. In any case the value differs from 41DB67A145000000 (internal repr.), decimal: 1839105300, the SAS datetime value '11APR2018:22:35:00'dt.

So, to resolve the issue, you should apply the ROUND function once the values have been read (from Excel) into SAS. If the datetime values actually don't contain fractions of a second, round to integers, otherwise round to 0.001 (seconds) or whatever the appropriate rounding unit for your data is.

Using these clean values in an export from SAS to a database minimizes the risk of introducing rounding errors in the target environment.

Edit: Cleaning the values also helps to avoid unexpected wrong results within SAS. For example, the IF condition in

if a>='11APR2018:22:35:00'dt then ...

would not be satisfied for a numeric value a with an internal HEX16. representation of 41DB67A144FFFFFF (or ...FFFE).