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
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.
@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
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".
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
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;
@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 |
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.
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...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.