Explicit Pass Through SQL Error

[DG1984]

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

[Kurt_Bremser]

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

[SuryaKiran]

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

[NinaL]

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 

[DG1984]

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

[Kurt_Bremser]

---

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

[Patrick]

---

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

[SuryaKiran]

Try %TSLIT

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

[DG1984]

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

[SuryaKiran]

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)

[LinusH]

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.

[Patrick]

@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')