Error in implicit pass-through

[UshaLatha]

I have  a code to select data of current and next month (for a particular year). But this would not work in December, as the values of months picked up will be 12 and 13:

proc sql;

create table test1 as

select distinct id from table A

where year(date)>= 2012

and (month(date)=(month(today()) + 1) OR (month(date)=month(today())));

quit;

So, now changed the code to following:

proc sql;

create table test2 as

select distinct id from table A

where year(date)>= 2012

and (month(intnx('month',date,0))=month(intnx('month',date,1,'s')) OR (month(date)=month(today()));

quit;

The values month(intnx('month',date,0)) and month(intnx('month',date,1,'s')) are coming correctly when testing in sas dataset. But its not working in Proc SQL.

The log says:

SAS_SQL: Unable to convert the query to DBMS specific SQL statement due to an error.

ACCESS_ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing.

Due to this the counts are not matching for test1 and test2 results (expecting same result counts for rundate 23OCT2018).

Please help resolve this.

[RW9]

intnx is a SAS function that is why.  You have to remember that SQL in a proc SQL when run locally is using the inbuilt SAS SQL compiler, which has access to the SAS functions.  If you are passing through the SQL code, then that code is taken as test to the database SQL compiler and run there, the SQL parser will not have the SAS functions.  Therefore you need to build your SQL using the destination platform, so if it is the database, then you would need to use the database functions to perform such a test, or write it yourself.