topic Re: [Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type date is invalid for argument 1 in SAS Programming

[Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type date is invalid for argument 1

Stretlow — Mon, 05 Dec 2016 11:33:55 GMT

Morning.

We have had a new data warehouse introduced recently which I connect to via ODBC for some reason which i cant explain SAS isnt recognising the dates in a table as a date (all show as $10.)

So what ive been doing in order to extract data is a substring on the character so for example if i wanted the last three months i could write

substr(trandate,1,7) in ("2016-11","2016-10","2016-09")

this works fine on some tables but not others and i get the following message

ERROR: CLI describe 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.

What is strange though is if i remove the substrring function and just search

Trandate = "2016-11-30"

it works fine....

I've done a little searching and while there are some discussions around the writing substrings on numeric variable this isnt the case here.

Any help greatly appreciated

Using sas 9.3

Re: [Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type date is invalid for argument 1

LinusH — Mon, 05 Dec 2016 11:57:08 GMT

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.

Re: [Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type date is invalid for argument 1

Stretlow — Mon, 05 Dec 2016 12:56:35 GMT

@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

Re: [Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type date is invalid for argument 1

LinusH — Mon, 05 Dec 2016 15:11:35 GMT

Options is a global statement, and even if it works placing in a data step, it's best practice to place them prior to the step that will use.

Can you also run the working where clause, so that we could compare?

I think that this behavior is strange though, and perhaps it comes back to what datatype SAS use for this column. The best wold be to use date on "both sides".

Re: [Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type date is invalid for argument 1

Stretlow — Mon, 05 Dec 2016 15:45:59 GMT

Hi... heres the logs 1st is the substr statement and the second is without.

1021
1022 options sastrace=',,,d' sastraceloc=saslog nostsuffix;
1023
ODBC: AUTOCOMMIT is NO for connection 5
ODBC: AUTOCOMMIT turned ON for connection id 5

ODBC_19: Prepared: on connection 5
SELECT * FROM "Integrity"."vwSalaryTransactions"

1024 data query;
1025 set DWH2.vwSalaryTransactions (keep = Trandate Accountkey Narrative1);
1026
1027 Where substr(Trandate,1,7) = "2016-11-30";
1028
1029 run;

ODBC_20: Prepared: on connection 5
SELECT "AccountKey", "Narrative1", "TranDate" FROM "Integrity"."vwSalaryTransactions" WHERE ({fn
SUBSTRING( "TranDate", 1, 7)} = '2016-11-30' )

ODBC: ROLLBACK performed on connection 5.
ERROR: CLI describe 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 3 variables.
WARNING: Data set WORK.QUERY was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.24 seconds
cpu time 0.03 seconds

1030
1031 options sastrace=',,,d' sastraceloc=saslog nostsuffix;
1032
ODBC: AUTOCOMMIT is NO for connection 5
ODBC: AUTOCOMMIT turned ON for connection id 5

ODBC_21: Prepared: on connection 5
SELECT * FROM "Integrity"."vwSalaryTransactions"

1033 data query;
1034 set DWH2.vwSalaryTransactions (keep = Trandate Accountkey Narrative1);
1035
1036 Where Trandate = "2016-11-30";
1037
1038 run;

ODBC_22: Prepared: on connection 5
SELECT "AccountKey", "Narrative1", "TranDate" FROM "Integrity"."vwSalaryTransactions" WHERE (
"TranDate" = '2016-11-30' )

ODBC_23: Executed: on connection 5
Prepared statement ODBC_22

NOTE: There were 104890 observations read from the data set DWH2.vwSalaryTransactions.
WHERE Trandate='2016-11-30';
NOTE: The data set WORK.QUERY has 104890 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 2.26 seconds
cpu time 0.21 seconds

Re: [Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type date is invalid for argument 1

ballardw — Mon, 05 Dec 2016 21:29:34 GMT

I suggest trying:

data query;
   set DWH2.vwSalaryTransactions (keep = Trandate Accountkey Narrative1 obs=10);
run;

Which would pull 10 records from the data set.

Then run Proc Contents on Query.

If the value is either a date or date time then the issue is with SUBSTR. Date values are going to be numeric and attempting to cast them to match a string value of "2016-11-30" is likely to be the culprit.

If the value is a datetime then you want to select using

Where datepart(trandate) = '30NOV2016'd; SAS will only recognize date literal values in the form of 'ddMONyy'd or 'ddMONyyyy'd (single or double quotes okay).

If the value is a date then

Where trandate = '30NOV2016'd;

Re: [Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type date is invalid for argument 1

Stretlow — Tue, 06 Dec 2016 08:01:37 GMT

@ballardw wrote:

I suggest trying:
data query;
   set DWH2.vwSalaryTransactions (keep = Trandate Accountkey Narrative1 obs=10);
run;
Which would pull 10 records from the data set.

Then run Proc Contents on Query.

If the value is either a date or date time then the issue is with SUBSTR. Date values are going to be numeric and attempting to cast them to match a string value of "2016-11-30" is likely to be the culprit.

If the value is a datetime then you want to select using

Where datepart(trandate) = '30NOV2016'd; SAS will only recognize date literal values in the form of 'ddMONyy'd or 'ddMONyyyy'd (single or double quotes okay).

If the value is a date then

Where trandate = '30NOV2016'd;

HI there.

Its definately a character variable...

SAS Output

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat	Label
1	AccountKey	Num	8	11.	11.	AccountKey
2	Narrative1	Char	100	$100.	$100.	Narrative1
3	TranDate	Char	10	$10.	$10.	TranDate

Re: [Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type date is invalid for argument 1

Stretlow — Thu, 05 Jan 2017 14:45:49 GMT

Anybody help any further with this, its causing me some big problems

Re: [Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type date is invalid for argument 1

GunnarTemnell — Tue, 23 May 2017 08:16:28 GMT

Try to add an compress to the datefield.

substr(trandate,1,7) in ("2016-11","2016-10","2016-09") ---> substr(compress(trandate),1,7) in ("2016-11","2016-10","2016-09")

It look like the where claues is applied on the result from odbc and not passed to sql server.

Re: [Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type date is invalid for argument 1

JBailey — Wed, 15 Aug 2018 18:29:19 GMT

Hi @Stretlow

This thread is very old and I doubt this is still an issue but this trick may help others.

It sounds like the date fields may be defined inconsistently in your database tables. This would explain why it would work differently for various columns.

It may help to take a look at them using SQL Server commands. Here is an example using everyone's favorite table - cars.

libname mssql sqlsvr complete="Driver={SAS ACCESS to SQL Server};
HOST='mysqlserver.mycompany.com';
UID=myuserid;PWD=mypassword;
DB=test"; 

data mssql.cars;
   set sashelp.cars;
run;

/*
  Use explicit pass-through to see how the columns are defined in the database.
*/

proc sql;
   connect using mssql;
   select * from connection to mssql
      (exec sp_columns cars);
quit;

This is the output...