topic Re: PROC SQL CLI Error in SAS Procedures

PROC SQL CLI Error

thegraduate — Thu, 30 May 2013 20:58:50 GMT

Hi All,

I have the following code which runs fine without the where statement. But as soon as I include it, I get an error:

%let
Basedate = %sysfunc(Today(),date9.); /*todays
date*/

%let
PrevMonthStartdate = %sysfunc(intnx(Month,
"&Basedate"d,-1,b), date9.); /*start date of the
previous month*/

%let
PrevMonthEnddate = %sysfunc(intnx(Month,
"&Basedate"d,-1,e), date9.); /*end date of the
previous month*/

%put
&prevmonthstartdate;

data
test;

set
XXX.TL_X;

format
date2 date9.;

date2=trxn_date;

run;

procsql;

connect to odbc (user="&mida_username."
password="&mida_password."
DSN="&mida_source.");

execute( Create table
LBG_REPORTING_1 as

select TRXN_DATE as TRANMONTH,
case    when CHANNELTYPE = '@SES'
then 'Digital'
when CHANNELTYPE in ('@CALLCENTER','@IVR')
then 'Telephony'

when CHANNELTYPE is null and TRANSACTIONTYPE in ('OTP','ENROLLMENT')
then 'Digi NM'

else 'Other' end as CHANNEL,TRANSACTIONTYPE as MODEL,

case when (TRXTYPECD = 'InternalTransfer' or TRXTYPECD
like '%Me2Me%') then 1
else 0 end as ME2ME,

ase when ISFILTERED = 'TRUE' then 1
else 0 end as ISFILTERED, TRXN_ID,CUSTID,AMOUNT

from
(select * from XXX.TL_X

/*where
distinct(trunc(TRXN_DATE,'MONTH')) BETWEEN TRUNC(ADD_MONTHS(SYSDATE, -1),'Month')
AND TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1)*/ where datepart(TRXN_DATE) = '&prevmonthstartdate'd
)a ) by odbc;

disconnectfrom odbc;

quit;

ERROR:
CLI execute error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthesis

Above is the error I get. I'm guessing I'm missing something obvious?

Thanks for your help guys.

Re: PROC SQL CLI Error

thegraduate — Thu, 30 May 2013 22:16:55 GMT

anyone?

Re: PROC SQL CLI Error

PGStats — Fri, 31 May 2013 00:18:53 GMT

Don't forget that everything in the Execute clause must be compatible with Oracle version of SQL. I don't know Oracle SQL but your WHERE clause looks very much like SAS SQL.

Re: PROC SQL CLI Error

art297 — Fri, 31 May 2013 00:59:50 GMT

I, too, am not familiar with Oracle SQL, but:

I doubt if the following is valid. Shouldn't it be starting with "case"?

ase when ISFILTERED = 'TRUE' then 1

Re: PROC SQL CLI Error

PGStats — Fri, 31 May 2013 01:56:07 GMT

Art, those lost characters are only the consequence of a copy and paste bug that shows up when pasting directly from SAS enhanced editor to MS Internet Explorer. I suspect the problem is either with the datepart function or the date litteral syntax in the WHERE clause.

Re: PROC SQL CLI Error

thegraduate — Fri, 31 May 2013 07:27:13 GMT

thanks guys.

So i've moved the datepart to the first datastep which resolves one potential issue, with regards to the date macro (the date must match to the first date of the previous month), does anyone know how to do this in oracle/sql?

Thanks,

Re: PROC SQL CLI Error

LinusH — Fri, 31 May 2013 08:11:46 GMT

If you want to get help on Oracle SQL syntax, you are in the wrong forum.

Why don't you try to use implicit SQL pass-thru, many SAS functions will be translated by the SAS/ACCESS engine.

Re: PROC SQL CLI Error

jwillis — Fri, 15 Aug 2014 14:51:52 GMT

I have not found a solution (other than moving the date selection criteria from ORACLE sql to SAS sql). I did find out that the problem is in Oracle. The same where code, that works for one of my source ORACLE databases, fails on the other of my source ORACLE databases.

/*** From GOOGLE:

This is an ORACLE "red herring" error that is common on some Oracle databases. A single quote or pair of single quotes needs to be two single quotes or two pairs of single quotes.

ERROR: Prepare error: ICommandPrepare::Prepare failed. : ORA-00907: missing right parenthesis

SQL statement: SELECT j.* FROM NCMR.VCI_NIC_NC_MAIN j where j.etq_created_date >= (to_date(01AUG2013:00:00:00,

'DDMONYYYY:HH:MI:SS')).

***/

Re: PROC SQL CLI Error

SASKiwi — Mon, 18 Aug 2014 20:18:07 GMT

Try something like this. Oracle, from memory, can cope with unambiguous date strings with hyphens and enclosed in single quotes.

%let PrevMonthStartdate = %str(%')%sysfunc(intnx(Month,"19AUG2014"d,-1,b),yymmdd10.)%str(%');

%put &PrevMonthStartdate;

SQL: where TRXN_DATE = &prevmonthstartdate

Re: PROC SQL CLI Error

jakarman — Mon, 18 Aug 2014 20:40:24 GMT

If you use explicit pass thruogh in the full approach to some RDBMS do not use anything that is common to SAS language. The RDBMS does not understand SAS language or any of those references. I See: where datepart(TRXN_DATE) = '&prevmonthstartdate'd.

If you wan the find the cause break the sql up in smaller understandable pieces.

Re: PROC SQL CLI Error

dataMart87 — Mon, 18 Aug 2014 21:51:57 GMT

Have you tried SQL_FUNCTIONS=ALL option? http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002155060.htm

Re: PROC SQL CLI Error

jakarman — Tue, 19 Aug 2014 05:48:12 GMT

Rommel that is a nice link, but did you notice: "Due to incompatibility in date and time functions between ODBC and SAS, ODBC might not process them correctly". The date, time and datetime conventions in several systems are not the same. A string common to sas as '... 'd has not a meaning in Oracle. An other one often causing problems is the "reserved words" list. These are also not equal, sometimes causing surprising results.

This list of SQL-fucntions is used with implicit pass thru in optimizing the SQL language translation form SAS Ansi to a native SQL dialect.
With explicit pass thru (execute statement) you are the one coding in a native SQL dialect. SAS has his hands off.

For debugging features.:

- sastrace SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition

- explain Using EXPLAIN PLAN If you want to know what happens when the sql is correct.
Do you have Oracle sql developer (free tool) you can develop and verify the Oracle Sql.

Re: PROC SQL CLI Error

dataMart87 — Tue, 19 Aug 2014 16:18:29 GMT

Thanks, Jaap. I don't speak Oracle SQL very well, and I don't have the developer tool. Looks interesting though that I just might research it a bit.