topic Re: Explicit Pass Through SQL Error in SAS Programming

Explicit Pass Through SQL Error

DG1984 — Wed, 28 Mar 2018 13:32:13 GMT

I am trying to submit this code

proc sql;
connect to odbc as mycon
(datasrc='xxxx' user='xxxx' password='xxxx' BULKLOAD=YES);
create table Table01 AS
select
*
from connection to mycon

(select *
from xxx.REQUEST
where DT_TRAN_CREATED >= "&maxdate"dt);
disconnect from mycon;
quit;

&maxdate is defined as 28MAR2018:14:12:48.00.

When I run this code I get this error RROR: PROC SQL requires any created table to have at least 1 column.

Any ideas on a solution - Thanks in advance

Re: Explicit Pass Through SQL Error

Kurt_Bremser — Wed, 28 Mar 2018 13:58:13 GMT

Your target database does not understand the SAS datetime literal; look at the DB documentation to see how datetime literals are expected to be.

Re: Explicit Pass Through SQL Error

SuryaKiran — Wed, 28 Mar 2018 14:00:11 GMT

Which database are you connecting to? Most databases don't allow double quotes for values.

Re: Explicit Pass Through SQL Error

NinaL — Wed, 28 Mar 2018 14:05:34 GMT

I believe you have confused implicit and explicit passthru in your coding. Take a look at my MWSUG paper from a few years ago, https://www.lexjansen.com/mwsug/2014/SA/MWSUG-2014-SA03.pdf , and try implicit passthru for simpler coding. If you must use explicit passthru, make sure to list your desired columns in the database's own syntax, including datetime format for the database, not SAS DT! Good luck!

Nina L. Werner

Re: Explicit Pass Through SQL Error

SuryaKiran — Wed, 28 Mar 2018 15:11:04 GMT

Try %TSLIT

where DT_TRAN_CREATED >= %TSLIT(&maxdate));

Re: Explicit Pass Through SQL Error

LinusH — Wed, 28 Mar 2018 15:56:25 GMT

For that query there's no need for explicit pass through. Use a libname and SAS will translate your datetime constant for you, and pass the WHERE clause to the database.

Re: Explicit Pass Through SQL Error

DG1984 — Wed, 28 Mar 2018 18:45:39 GMT

Many thanks - although I get the error below

ERROR: CLI cursor fetch error: [Oracle][ODBC][Ora]ORA-01858: a non-numeric character was found where a numeric was expected

Re: Explicit Pass Through SQL Error

SuryaKiran — Wed, 28 Mar 2018 19:51:11 GMT

Change you &maxdate macro value to a format Oracle knows like 28-MAR-2018 14:12:48. and in your filter change the Oracle Datetime values to Characters using Oracle function TO_CHAR.

where TO_CHAR(DT_TRAN_CREATED,'DD-MON-YY HH24:MI:SS') >= %TSLIT(&maxdate));

Is there any specific reason for using explicit pass-through, instead of Implicit way (Using LIBNAME)

Re: Explicit Pass Through SQL Error

Patrick — Wed, 28 Mar 2018 20:06:14 GMT

@DG1984

You need to convert your date string into something Oracle can understand. Below code worked for me in an actual project.

%let maxdate=28MAR2018:14:12:48.00;
%let maxdate_ora=to_date(%unquote(%str(%')&maxdate%str(%')),'DDMONYYYY:HH24:MI:SS');

You then can use above in one of below ways:

where DT_TRAN_CREATED >= &maxdate_ora

or..

where DT_TRAN_CREATED >= to_date(%unquote(%str(%')&maxdate%str(%')),'DDMONYYYY:HH24:MI:SS')

Re: Explicit Pass Through SQL Error

DG1984 — Thu, 29 Mar 2018 08:24:45 GMT

Thanks for the paper, I found it online yesterday and found it useful. I am using explicit and not implicit because the tables I am querying have a lot of data and I only want a small section of that data.

Thanks

Re: Explicit Pass Through SQL Error

Kurt_Bremser — Thu, 29 Mar 2018 09:06:57 GMT

@DG1984 wrote:

Thanks for the paper, I found it online yesterday and found it useful. I am using explicit and not implicit because the tables I am querying have a lot of data and I only want a small section of that data.

Thanks

At least run a test of both methods to verify that explicit PT is needed. You might find that in many/most cases it's not necessary, if you're just doing simple subsets.

Re: Explicit Pass Through SQL Error

Patrick — Thu, 29 Mar 2018 12:00:11 GMT

@DG1984 wrote:

Thanks for the paper, I found it online yesterday and found it useful. I am using explicit and not implicit because the tables I am querying have a lot of data and I only want a small section of that data.

Thanks

@DG1984

The SAS Access engine will try and push as much of the processing to the database as it manages.

Use the following set of SAS options at the beginning of your code. This will then show you in the log what actually gets executed on the database side and what on the SAS side.

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

For your code here there is a high chance the access engine will be able to fully convert your SQL to the database flavour and though all processing will happen in-database.