CODEWITHSUNDEEP
sql-serversas
PNPTestovir
PNPTestovir

Reputation: 307

WHERE clause operator requires compatible variables

Below script (fx is SQL Server's table):

LIBNAME SQL ODBC DSN='sql server' ;

DATA new;
    SET SQL.fx;
    WHERE repo_date = '2016-04-29 00:00:00.000';
RUN;


PROC PRINT DATA=new;
RUN;

returns me an error (in SAS log):

191      WHERE repo_date = '2016-04-29 00:00:00.000';
ERROR: WHERE clause operator requires compatible variables.
192  RUN;

Where can I check which data conversion I need (in this case and in others)?

In SQL Server 2008R2 repo_date is a datetime column.

Upvotes: 2

Views: 5635

Answers (2)

Stu Sztukowski
Stu Sztukowski

Reputation: 12909

SAS is using the ODBC libname engine to translate SAS data step code into SQL code. Because you're writing it in SAS, SAS is assuming that you are looking for the string 2016-04-29 00:00:00.000. Instead, you want to put it in a SAS date literal so SAS knows how to translate the data.

LIBNAME SQL ODBC DSN='sql server' ;

DATA new;
    SET SQL.fx;
    WHERE repo_date = '29APR2016:00:00:00'dt;
RUN;

PROC PRINT DATA=new;
RUN;

If you were doing SQL passthrough to directly run SQL on the server, then your above code would work.

proc sql noprint;
    connect to odbc(datasrc='sql server');
        create table new as
            select * from connection to odbc
                (select * from schema.fx where repo_date='2016-04-29 00:00:00.000');
    disconnect from odbc;
quit;

Basically, what the above is doing is having the SQL server pull the columns, then SAS simply pulls it all over to itself. Think of it as using SAS as a proxy program to run commands directly on the SQL server.

Upvotes: 2

kl78
kl78

Reputation: 1666

You are comparing a string to a numeric value. So your datetime-format is wrong (like Heinzi mentioned), and also you have to convert it to a datetime value (by adding a dt at the end)

Working should this:

WHERE repo_date ='29APR2016 00:00:00.000'dt;

If repo_time is datetime and the time is not relevant, you can just compare the date:

WHERE datepart(repo_date) = '29APR2016'd;

Upvotes: 4

Related Questions