CLI Error on Pass-through to Redshift

[rsheehanBJS]

%_eg_conditional_dropds(WORK.QUERY_FOR_PURCHASE_DETAIL);

PROC SQL;
CONNECT TO ODBC as con1
(READBUFF=3000 DATAsrc="Amazon Redshift DSN 64" user=USER password=PASS);

CREATE TABLE WORK.QUERY_FOR_PURCHASE_DETAIL AS
SELECT *
FROM CONNECTION TO con1 (
SELECT "t1"."mbrshp_nbr",
"t1"."purch_dt",
"t1"."article_nbr"
FROM "PURCHASE_DETAIL" "t1"
WHERE "t1"."sales_ctgry_cd" = '03' AND "t1"."purch_dt" >= '1Jan2018'd);
DISCONNECT FROM con1;
QUIT;

%_eg_conditional_dropds(WORK.QUERY_FOR_PURCHASE_DETAIL);

PROC SQL;
CONNECT TO ODBC as con1
(READBUFF=3000 DATAsrc="Amazon Redshift DSN 64" user=USER password=PASS);

CREATE TABLE WORK.QUERY_FOR_PURCHASE_DETAIL AS
SELECT *
FROM CONNECTION TO con1 (
SELECT t1.mbrshp_nbr,
t1.purch_dt,
t1.article_nbr
FROM t_midw.PURCHASE_DETAIL t1
WHERE t1.sales_ctgry_cd = '03' AND t1.purch_dt >= '1Jan2018'd);
DISCONNECT FROM con1;
QUIT;

LOG

1 The SAS System 12:12 Thursday, November 8, 2018

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Code For Query Builder';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=ACTIVEX;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HTMLBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;

26 PROC SQL;
27 CONNECT TO ODBC as con1
28 (READBUFF=3000 DATAsrc="Amazon Redshift DSN 64" user=svc_sasusr password=XXXXXXXXXXX);
29
30 CREATE TABLE WORK.QUERY_FOR_PURCHASE_DETAIL AS
31 SELECT *
32 FROM CONNECTION TO con1 (
33 SELECT t1.mbrshp_nbr,
34 t1.purch_dt,
35 t1.article_nbr
36 FROM t_midw.PURCHASE_DETAIL t1
37 WHERE t1.sales_ctgry_cd = '03' AND t1.purch_dt >= '1Jan2018'd);
ERROR: CLI prepare error: Unable to retrieve error message.
SQL statement: SELECT t1.mbrshp_nbr, t1.purch_dt, t1.article_nbr FROM t_midw.PURCHASE_DETAIL t1 WHERE t1.sales_ctgry_cd = '03' AND
t1.purch_dt >= '1Jan2018'd.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
38 %put &SQLXRC;
-1
39 %put &SQLXMSG;
ERROR: CLI prepare error: Unable to retrieve error message.SQL statement: SELECT t1.mbrshp_nbr, t1.purch_dt, t1.article_nbr FROM
t_midw.PURCHASE_DETAIL t1 WHERE t1.sales_ctgry_cd = '03' AND t1.purch_dt >= '1Jan2018'd.
40 DISCONNECT FROM con1;
NOTE: Statement not executed due to NOEXEC option.
41 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.05 seconds
cpu time 0.02 seconds

42
2 The SAS System 12:12 Thursday, November 8, 2018

43 GOPTIONS NOACCESSIBLE;
44 %LET _CLIENTTASKLABEL=;
45 %LET _CLIENTPROCESSFLOWNAME=;
46 %LET _CLIENTPROJECTPATH=;
47 %LET _CLIENTPROJECTPATHHOST=;
48 %LET _CLIENTPROJECTNAME=;
49 %LET _SASPROGRAMFILE=;
50 %LET _SASPROGRAMFILEHOST=;
51
52 ;*';*";*/;quit;run;
53 ODS _ALL_ CLOSE;
54
55
56 QUIT; RUN;
57

[Vince_SAS]

Perhaps the problem is that you are passing a SAS date literal to the 3rd-party database:

WHERE t1.sales_ctgry_cd = '03' AND t1.purch_dt >= '1Jan2018'd

Try using a Redshift date value in the comparison.  You need to check the Redshift documentation, but maybe something like this:

where t1.sales_ctgry_cd = '03'and t1.purch_dt >= '2018-01-01'

Or this:

where t1.sales_ctgry_cd = '03'and t1.purch_dt >= to_date('01 Jan 2018', 'DD Mon YYYY')

Vince DelGobbo

SAS R&D

[rsheehanBJS]

I'll give it a try but we have other programs that don't use a date parameter and bomb on memory.  When we were going to Teradata we never had these issues only on redshift and everything is sized the same.

Bob