Help using Base SAS procedures

PROC SQL CLI Error

Reply
Contributor
Posts: 24

PROC SQL CLI Error

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.

Contributor
Posts: 24

Re: PROC SQL CLI Error


anyone?

Respected Advisor
Posts: 4,659

Re: PROC SQL CLI Error

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.

PG

PG
PROC Star
Posts: 7,364

Re: PROC SQL CLI Error

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

Respected Advisor
Posts: 4,659

Re: PROC SQL CLI Error

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.

PG

PG
Contributor
Posts: 24

Re: PROC SQL CLI Error

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,

Super User
Posts: 5,260

Re: PROC SQL CLI Error

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.

Data never sleeps
Regular Contributor
Posts: 217

Re: PROC SQL CLI Error

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:Smiley Tonguerepare 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:MISmiley FrustratedS')).

***/

Super User
Posts: 3,114

Re: PROC SQL CLI Error

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

Valued Guide
Posts: 3,208

Re: PROC SQL CLI Error

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.


---->-- ja karman --<-----
Contributor
Posts: 35

Re: PROC SQL CLI Error

Valued Guide
Posts: 3,208

Re: PROC SQL CLI Error

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.

---->-- ja karman --<-----
Contributor
Posts: 35

Re: PROC SQL CLI Error

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.

Ask a Question
Discussion stats
  • 12 replies
  • 1761 views
  • 0 likes
  • 8 in conversation