topic Re: Incorrect Syntax near the keyword 'WHERE' in SAS Data Management

Incorrect Syntax near the keyword 'WHERE'

Daylon_Hunt — Fri, 08 Apr 2016 22:02:41 GMT

I have a job that is erroring out at the very end, and getting an error at the keyword "WHERE". It's using a Subquery to extract the most recent row processing date, and using that date as a filter for bringing in the source data. Below is the code that is erroring out:

from
bnkDbDataGov.V_DMI_CIF_Daily as V_DMI_CIF_Daily,
(
select
MAX(V_DMI_CIF_Daily_sub.DWDATE_Int ) as Max_Date
from
bnkDbDataGov.V_DMI_CIF_Daily as V_DMI_CIF_Daily_sub
)
where
V_DMI_CIF_Daily.DWDATE_Int = Max_Date

);

%rcSet(&sqlrc);

disconnect from ODBC;
quit;

Re: Incorrect Syntax near the keyword 'WHERE'

jklaverstijn — Fri, 08 Apr 2016 22:13:10 GMT

Would you like to share the log with the complete code and error massages? That wouldgreatly help others Help you.

Cheets Jan.

Re: Incorrect Syntax near the keyword 'WHERE'

ballardw — Fri, 08 Apr 2016 22:19:34 GMT

In your code

from
bnkDbDataGov.V_DMI_CIF_Daily as V_DMI_CIF_Daily, 
(
select
MAX(V_DMI_CIF_Daily_sub.DWDATE_Int ) as Max_Date
from
bnkDbDataGov.V_DMI_CIF_Daily as V_DMI_CIF_Daily_sub
)
where
V_DMI_CIF_Daily.DWDATE_Int = Max_Date

);

From what you post the last ) does not have a matching ( .

The log is always a good idea as it is often pretty good about showing where issues are.

Re: Incorrect Syntax near the keyword 'WHERE'

LinusH — Fri, 08 Apr 2016 22:24:14 GMT

The "disconnect from ODBC" tells me that the problem i s probably related to some syntax within your (guessing here) ...from connection to-block. So technically, it may not be a SAS issue.

Re: Incorrect Syntax near the keyword 'WHERE'

Daylon_Hunt — Fri, 08 Apr 2016 23:09:38 GMT

Attached is the log, and the error is occurring at row 1320

Re: Incorrect Syntax near the keyword 'WHERE'

LinusH — Sat, 09 Apr 2016 00:02:11 GMT

Ok so it's Join transformations with explicit pass thru. Looks like it's generated code, right?
Even so, it's possible to generate non syntactically correct SQL. Try the pass thru part in SQL Server Management Studio.

Why are you using explicit pass thru?
Looks like your query could easily be restructured from the in line view join to group by-having. By use that simple syntax chances are pretty high that it will be passed thru implicitly and that would hopefully solve your syntax problem.

Re: Incorrect Syntax near the keyword 'WHERE'

LinusH — Sat, 09 Apr 2016 11:36:30 GMT

Edit: not GROUP BY - just HAVING. 😳

Re: Incorrect Syntax near the keyword 'WHERE'

Daylon_Hunt — Mon, 11 Apr 2016 20:21:12 GMT

Using the "Having" instead of "Where" appears to have done the trick. Although, it wouldn't work without using the "Group By" too. I still feel like I'm not going about this particular job in the most efficient way, I'm learning a lot as I go.