BookmarkSubscribeRSS Feed
thegraduate
Calcite | Level 5

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.

12 REPLIES 12
thegraduate
Calcite | Level 5


anyone?

PGStats
Opal | Level 21

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
art297
Opal | Level 21

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

PGStats
Opal | Level 21

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
thegraduate
Calcite | Level 5

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,

LinusH
Tourmaline | Level 20

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
jwillis
Quartz | Level 8

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

***/

SASKiwi
PROC Star

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

jakarman
Barite | Level 11

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 --<-----
jakarman
Barite | Level 11

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 --<-----
dataMart87
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 4496 views
  • 0 likes
  • 8 in conversation