BookmarkSubscribeRSS Feed
Stretlow
Obsidian | Level 7

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

9 REPLIES 9
LinusH
Tourmaline | Level 20

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
Stretlow
Obsidian | Level 7

@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

 

 

LinusH
Tourmaline | Level 20

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
Stretlow
Obsidian | Level 7

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

 

 

 

 

 

ballardw
Super User

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;

Stretlow
Obsidian | Level 7

@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
Stretlow
Obsidian | Level 7
Anybody help any further with this, its causing me some big problems
GunnarTemnell
Calcite | Level 5

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.

 

 

JBailey
Barite | Level 11

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

 

sql_server_describe_table.png

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3133 views
  • 0 likes
  • 5 in conversation