topic Re: SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error. in New SAS User

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

DimaSup — Mon, 17 May 2021 09:16:12 GMT

SYMBOLGEN: Macro variable T_CLIENT_ID resolves to 00000031-B081-4E97-9437-F20CF874F857

proc sql;
select
COALESCE(sum(abc.step_sum),0)
into :SumLoans_12m
from
RTDM_ABT.ABT_CONTRACT abc
where abc.CLIENT_ID = "&T_CLIENT_ID"
and today() - datepart(abc.CONTRACT_BEGINDATE) <= 365
and abc.DML_FLAG NE 1
group by abc.CLIENT_ID;
quit;

(Format CONTRACT_BEGINDATE = 2021-01-14 00:00:00.000)

ERROR:

SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error. 152499 1621241630 no_name 0 SQL (437
ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing. 152500 1621241630 no_name 0 SQL (437
152501 1621241630 no_name 0 SQL (437

its work fo t-sql:

select coalesce(sum(step_sum),0)
from
RTDM_ABT.ABT_CONTRACT abc
where abc.client_id = '00000AAB-F0E1-4CFA-9D91-C598FCDFAB4F'
and GETDATE() - abc.CONTRACT_BEGINDATE <= 365
and abc.DML_FLAG <> 1
group by abc.client_id

where's my mistake? Thanks for your help!

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

AMSAS — Mon, 17 May 2021 10:54:09 GMT

Based on the error message I'm assuming you are doing SQL Pass-Thru to a DBMS i.e. the SQL code is run inside the DBMS and not run by SAS.
The error message is telling you that the SQL contains some SAS syntax that the DBMS does not understand, so the query is passed back to SAS and SAS runs the query.
My educated guess is the following function calls are probably the issue:

and today() - datepart(abc.CONTRACT_BEGINDATE) <= 365

I'd start by removing or changing those to a hardcoded value and see if the query works

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

DimaSup — Mon, 17 May 2021 11:28:15 GMT

I also had such an assumption and it was confirmed, after deleting this line, the code works, but how to make this script work with this line is not yet clear to me. 🤔

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

AMSAS — Mon, 17 May 2021 16:46:40 GMT

So now we know that's the problem, the question is how to correct the code.

The first thing we need to know is the format of abc.CONTRACT_BEGINDATE, as you're using the datepart() function I would assume it's a SAS date/time value. If it is a SAS data/time value then I think the following would work (untested):


data _null_ ;
	now=datetime() ;
	diff=365*24*60*60 ;
	put now= datetime. now= ; 
	call symput("mvNow",put(now,14.2)) ;
run ;

%put mvNow= &mvNow ;
proc sql;
select
COALESCE(sum(abc.step_sum),0)
into :SumLoans_12m
from
RTDM_ABT.ABT_CONTRACT abc
where abc.CLIENT_ID = "&T_CLIENT_ID"
and &mvNow - abc.CONTRACT_BEGINDATE  <= (365*24*60*60)
and abc.DML_FLAG NE 1
group by abc.CLIENT_ID;
quit;

I'm not certain this will work, as I do not use SQL pass-thru, and I'm not sure if the SAS Macro Variable will be resolved, prior to the code being passed to DBMS (I believe it does).
You might also want to change the (365*24*60*60) to 31536000 (number of seconds in 365 days)
Again this assumes abc.CONTRACT_BEGINDATE is a SAS date/time value (number of seconds since 00:00:00 01Jan1960)

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

DimaSup — Tue, 18 May 2021 07:16:09 GMT

Errors:

SAS_SQL: Failed to get a DBMS-specific datetime value. 163681 1621320885 no_name 0 SQL (469
SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error. 163682

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

DimaSup — Tue, 18 May 2021 07:18:51 GMT

CONTRACT_BEGINDATE ( 2021-01-14 00:00:00.000 )

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

DimaSup — Tue, 18 May 2021 07:20:14 GMT

Full log:
The SAS System

memory 265.37k
OS Memory 29476.00k
Timestamp 18.05.2021 09:54:45
Page Faults 0
Page Reclaims 136
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

MLOGIC(MEQUIBEHSCOREUPRE): %PUT mvNow= &mvNow
SYMBOLGEN: Macro variable MVNOW resolves to 1936950885.51
mvNow= 1936950885.51
MPRINT(MEQUIBEHSCOREUPRE): proc sql;
SYMBOLGEN: Macro variable T_CLIENT_ID resolves to 00000031-B081-4E97-9437-F20CF874F857
SYMBOLGEN: Macro variable MVNOW resolves to 1936950885.51
MPRINT(MEQUIBEHSCOREUPRE): select COALESCE(sum(abc.step_sum),0) into :SumLoans_12m from RTDM_ABT.ABT_CONTRACT abc where abc.CLIENT_ID = "00000031-B081-4E97-9437-F20CF874F857" and 1936950885.51
- abc.CONTRACT_BEGINDATE <= (365*24*60*60) and abc.DML_FLAG NE 1 group by abc.CLIENT_ID;
163677 1621320885 no_name 0 SQL (469
SQLSRV_37146: Prepared: on connection 0 163678 1621320885 no_name 0 SQL (469
SELECT * FROM rtdm_abt . ABT_CONTRACT 163679 1621320885 no_name 0 SQL (469
163680 1621320885 no_name 0 SQL (469
SAS_SQL: Failed to get a DBMS-specific datetime value. 163681 1621320885 no_name 0 SQL (469
SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error. 163682 1621320885 no_name 0 SQL (469
ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing. 163683 1621320885 no_name 0 SQL (469
163684 1621320885 no_name 0 SQL (469
SQLSRV_37147: Prepared: on connection 0 163685 1621320885 no_name 0 SQL (469
SELECT STEP_SUM , CLIENT_ID , CONTRACT_BEGINDATE , DML_FLAG FROM rtdm_abt . ABT_CONTRACT WHERE ( ( CLIENT_ID = '00000031-B081-4E97-9437-F20CF874F857' ) AND ( DML_FLAG <> 1 ) )
163686 1621320885 no_name 0 SQL (469
163687 1621320885 no_name 0 SQL (469
163688 1621320885 no_name 0 SQL (469
SQLSRV_37148: Executed: on connection 0 163689 1621320885 no_name 0 SQL (469
Prepared statement SQLSRV_37147 163690 1621320885 no_name 0 SQL (469
163691 1621320885 no_name 0 SQL (469
NOTE: No rows were selected.
MPRINT(MEQUIBEHSCOREUPRE): quit;

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

DimaSup — Tue, 18 May 2021 09:22:38 GMT

I understand it is necessary to convert CONTRACT_BEGINDATE (2021-01-14 00:00: 00.000) to format SAS, I think it would help

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

Kurt_Bremser — Tue, 18 May 2021 11:52:26 GMT

Why dou use the DATEPART function on something that seems to be a date already?

You also do not need the GROUP BY when your WHERE condition already restricts the observations to one group.

And you may have a problem converting the UUID string to the correct type in the DBMS.

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

DimaSup — Tue, 18 May 2021 12:53:27 GMT

Yes, Hooked from the test script, thanks for the remark. But this does not solve the mistake.
And it doesn't work for me with and without datepart()

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

DimaSup — Thu, 20 May 2021 10:11:53 GMT

proc sql noprint;
select
sum(step_sum)
into :SumLoans_12m
from
RTDM_ABT.ABT_CONTRACT abc
where abc.client_id = "&T_CLIENT_ID"
and abc.DML_FLAG <> 1
and datepart(abc.CONTRACT_BEGINDATE) between today() - 365 and today();
quit;

It worked for me, in double it's nice that I came up with it myself ))