DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 49

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

[ Edited ]

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

Super User
Posts: 5,257

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

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.

Data never sleeps
Contributor
Posts: 49

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


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

 

 

Super User
Posts: 5,257

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

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". 

Data never sleeps
Contributor
Posts: 49

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

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

 

 

 

 

 

Super User
Posts: 10,514

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

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;

Contributor
Posts: 49

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


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
Contributor
Posts: 49

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

Anybody help any further with this, its causing me some big problems
New User
Posts: 1

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

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.

 

 

Ask a Question
Discussion stats
  • 8 replies
  • 309 views
  • 0 likes
  • 4 in conversation