@LinusH wrote:
On reason could be that SAS tries to push part of the query down to thwe DBMS.
Try
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
and see how these two different SAS code translates to SQL Server SQL.
Hi.
Thanks for respoinding. I've nver seen that before, Im assuming that just goes within the datastep?
if so this is what the log shows.
883 ODBC: AUTOCOMMIT is NO for connection 5 ODBC: AUTOCOMMIT turned ON for connection id 5
ODBC_7: Prepared: on connection 5 SELECT * FROM "Integrity"."vwSalaryTransactions"
884 data query; 885 set DWH2.vwTransactions; 886 887 Where substr(Trandate,1,7) = "2016-11"; 888 889 options sastrace=',,,d' sastraceloc=saslog nostsuffix; 890 891 run;
ODBC_8: Prepared: on connection 5 SELECT "AccountKey", "BankNumber", "AccountNumber", "TranDate", "AgentUserID", "KnownAs", "TeamName", "FirstName", "Surname", FROM "Integrity"."vwTransactions" WHERE ({fn SUBSTRING( "TranDate", 1, 7)} = '2016-11' )
ODBC_9: Executed: on connection 5 Prepared statement ODBC_8
ERROR: CLI open cursor error: [Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type date is invalid for argument 1 of SUBSTRING function. : [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. NOTE: The DATA step has been abnormally terminated. NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.QUERY may be incomplete. When this step was stopped there were 0 observations and 65 variables. WARNING: Data set WORK.QUERY was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.32 seconds cpu time 0.01 seconds
... View more