DATA Step, Macro, Functions and more

Explicit Pass Through SQL Error

Reply
Occasional Contributor
Posts: 8

Explicit Pass Through SQL Error

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

 

 

Super User
Posts: 9,890

Re: Explicit Pass Through SQL Error

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Valued Guide
Posts: 559

Re: Explicit Pass Through SQL Error

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

Thanks,
Suryakiran
Senior User
Posts: 1

Re: Explicit Pass Through SQL Error

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 

Occasional Contributor
Posts: 8

Re: Explicit Pass Through SQL Error

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

Super User
Posts: 9,890

Re: Explicit Pass Through SQL Error


@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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 4,678

Re: Explicit Pass Through SQL Error


@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.

 

Valued Guide
Posts: 559

Re: Explicit Pass Through SQL Error

Try %TSLIT

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

 

Thanks,
Suryakiran
Occasional Contributor
Posts: 8

Re: Explicit Pass Through SQL Error

Posted in reply to SuryaKiran

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

Valued Guide
Posts: 559

Re: Explicit Pass Through SQL Error

[ Edited ]

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)

Thanks,
Suryakiran
Super User
Posts: 5,852

Re: Explicit Pass Through SQL Error

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.
Data never sleeps
Respected Advisor
Posts: 4,678

Re: Explicit Pass Through SQL Error

[ Edited ]

@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')
Ask a Question
Discussion stats
  • 11 replies
  • 145 views
  • 5 likes
  • 6 in conversation