CLI Error on Pass-through to Redshift

rsheehanBJS — Thu, 08 Nov 2018 18:21:34 GMT

%_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

Re: CLI Error on Pass-through to Redshift

Vince_SAS — Sun, 11 Nov 2018 20:40:45 GMT

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

Re: CLI Error on Pass-through to Redshift

rsheehanBJS — Tue, 27 Nov 2018 12:54:34 GMT

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

topic CLI Error on Pass-through to Redshift in New SAS User

CLI Error on Pass-through to Redshift

Re: CLI Error on Pass-through to Redshift

Re: CLI Error on Pass-through to Redshift