Update on this issue: We found out that the error message only occurs when the OPTION sastrace=',d' is used. Without this trace option, there is no error message. We have created a SAS track on this issue and other releated issues. SAS is investigating the issues. Besides the error message, we found out that: - SAS converts a SAS variable differently in an INSERT statement then in an UPDATE statement. This occurs with the SQLSVR engine, on SAS 9.4 M6 on AIX, with SQL datetime, datetime2(3) and datetime(7) datatypes. Only datetime(4) works as expected. Code to reproduces these issues: /*OPTIONS DBIDIRECTEXEC NOSQLIPONEATTEMPT sastrace='d,,,d' sastraceloc=saslog nostsuffix pagesize=max linesize=max;*/
OPTIONS DBIDIRECTEXEC NOSQLIPONEATTEMPT sastrace='d,' sastraceloc=saslog nostsuffix pagesize=max linesize=max;
%macro Dummy;
%IF &SYSSCPL. EQ AIX %THEN
%DO;
LIBNAME BI SQLSVR Datasrc=BI2 SCHEMA=dbo datetime2=yes;
%LET ENGINE=SQLSVR;
%END;
%ELSE
%DO;
LIBNAME BI OLEDB BULKLOAD=YES PROPERTIES=("Integrated Security"=SSPI "Persist Security Info"=True "Initial Catalog"=sas_bi) DATASOURCE=DB_TST_SAS_BI PROVIDER=SQLNCLI10.1 SCHEMA=dbo;
%LET ENGINE=OLEDB;
%END;
PROC SQL;
CONNECT using BI;
* Pass-Through Facility;
execute(
drop table test;
CREATE TABLE TEST(
ID INT,
DT_Start datetime NULL,
DT_End datetime NULL,
DT2_3_Start datetime2(3) NULL,
DT2_3_End datetime2(3) NULL,
DT2_4_Start datetime2(4) NULL,
DT2_4_End datetime2(4) NULL,
DT2_7_Start datetime2(7) NULL,
DT2_7_End datetime2(7) NULL,
) ) BY BI;
;
QUIT;
%MEND;
%Dummy;
%LET sysProcesDatum=1901461843.9547;
%let sysProcesDatum3=%str(%')%sysfunc(putn(&SYSPROCESDATUM, e8601dt23.3))%str(%');
%let sysProcesDatum4=%str(%')%sysfunc(putn(&SYSPROCESDATUM, e8601dt23.4))%str(%');
%put &=sysProcesDatum3;
%put &=sysProcesDatum4;
PROC SQL;
DELETE FROM BI.TEST;
QUIT;
PROC SQL;
INSERT INTO BI.TEST (id, DT_Start, DT2_3_Start, DT2_4_Start,DT2_7_Start)
VALUES(1, &sysProcesDatum.,&sysProcesDatum.,&sysProcesDatum.,&sysProcesDatum.);
QUIT;
PROC SQL;
SELECT * FROM BI.TEST;
QUIT;
PROC SQL;
UPDATE BI.TEST
SET DT_End = &sysProcesDatum.
WHERE DT_Start = &sysProcesDatum.
AND ID=1;
QUIT;
PROC SQL;
SELECT * FROM BI.TEST;
QUIT;
PROC SQL;
UPDATE BI.TEST
SET DT2_3_End = &sysProcesDatum.
WHERE DT2_3_Start = &sysProcesDatum.
AND ID=1;
QUIT;
PROC SQL;
SELECT * FROM BI.TEST;
QUIT;
PROC SQL;
UPDATE BI.TEST
SET DT2_4_End = &sysProcesDatum.
WHERE DT2_4_Start = &sysProcesDatum.;
QUIT;
PROC SQL;
SELECT * FROM BI.TEST;
QUIT;
PROC SQL;
UPDATE BI.TEST
SET DT2_7_End = &sysProcesDatum.
WHERE DT2_7_Start = &sysProcesDatum.;
QUIT;
PROC SQL;
SELECT * FROM BI.TEST;
QUIT;
... View more