topic Re: In-database processsing Error in SAS Programming

In-database processsing Error

David_Billa — Wed, 08 Jan 2020 07:19:14 GMT

I'm getting the following error while I run the SAS code which query SQL table. It is worth to inform that the code which I ran contains put function to convert data field to character with yymmd8. Format. Is it OK to apply only the Options

options SQLMAPPUTTO=NONE to get rid of this error?

Please be informed that my SAS code contains multiple Proc SQL's in which few, query the SQL table and it contains PUT function. So I've to put only options SQLMAPPUTTO=NONE when it contains PUT function for SQL tables and for other query can I mention options SQLMAPPUTTO=SAS_PUT?

ERROR: Error during in-database processing.
ERROR: Error during in-database processing.
ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near 'dbo'.
ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of compile 
       errors.
ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near 'dbo'.
ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of compile

Re: In-database processsing Error

LinusH — Wed, 08 Jan 2020 07:55:13 GMT

Can you please provide the full code?

Re: In-database processsing Error

David_Billa — Wed, 08 Jan 2020 08:00:49 GMT

I can summarize the code for you as I left from office.

I'm querying the SQL server table via proc sql and that query has put
function (e.g. put(joining_date,yymmd8.) as joining_date for one variable.

Same code works fine if the contributing table is SAS dataset.

Re: In-database processsing Error

Kurt_Bremser — Wed, 08 Jan 2020 08:46:26 GMT

If you use explicit pass-through, the code sent to the DB must use the DMBS's syntax.

Re: In-database processsing Error

David_Billa — Wed, 08 Jan 2020 09:36:08 GMT

It's a implicit passthrough.

Re: In-database processsing Error

Kurt_Bremser — Wed, 08 Jan 2020 10:05:49 GMT

Then I think we really need to see the code here.

Re: In-database processsing Error

David_Billa — Wed, 08 Jan 2020 10:43:18 GMT

Code is here @LinusH @Kurt_Bremser

proc sql;
   create table work.OUTPUT as
      select distinct
         ID,
         CAUSE,
         (put(REPORTING_DATE ,yymmd8.)) as REPORTING_DATE length = 8
            format = $8.
            label = 'REPORTING_DATE'
from sqllbry.customer;
quit;

Re: In-database processsing Error

Kurt_Bremser — Wed, 08 Jan 2020 10:51:31 GMT

There's a "d" missing in your format name, and you should use yymmddn8. to avoid getting a 2-digit year.

Re: In-database processsing Error

David_Billa — Wed, 08 Jan 2020 11:08:26 GMT

still I'm getting the error after changing the Format to yymmddn8.

ERROR: Numeric format YYMMDDN in PUT function requires a numeric argument.
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE 
      expression.
ERROR: Numeric format YYMMN in PUT function requires a numeric argument.
ERROR: Numeric format YYMMN in PUT function requires a numeric argument.

Re: In-database processsing Error

Kurt_Bremser — Wed, 08 Jan 2020 11:45:07 GMT

Which means that reporting_date is character, and not a date, at least as SAS sees it.

I also see no CASE clause in the code you posted, so we seem to be talking about different things here. For clear diagnosis, we need the code, the whole code, and nothing but the code. Please run your code with

options sastrace=',,,d' sastraceloc=saslog;

and post the whole log of the step.

Re: In-database processsing Error

LinusH — Wed, 08 Jan 2020 14:58:08 GMT

Yes, what is the format of your reporting_date column?

You probably need to first do an input(reporting_date, whatever format your date column is in.)

Re: In-database processsing Error

David_Billa — Wed, 08 Jan 2020 15:12:05 GMT

Reporting_date field is numeric

Re: In-database processsing Error

_alex — Fri, 22 Nov 2024 10:19:14 GMT

thx david for providing the solution with
SQLMAPPUTTO=NONE
it saved my day and solved the above error.