DATA Step, Macro, Functions and more

Oracle Dates filter not working with a SAS to Oracle Passthrough

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

Oracle Dates filter not working with a SAS to Oracle Passthrough

 

 Good Morning SAS World,

 

I am absolutely baffled by this error, as i have written something previously using the Date filters and it seemed to work fine, but with the below code i am getting back dates before the quoted range.

 

The purpose of this code is to pull transactions back with automated dates for the previous day only - however, it pull back everything that matches on the name logic regardless of the date

 

I.e if today is

12MAY2017

i want everything from the 11th - which is what the macro dates provide , but the below code pull entries back from 2013 for example

 

I have also included a COMMENTED OUT TO_DATE - nrbquote date filter, is this any better, if so what is wrong with mine?

 

Much appreciated as always

 

/*GET PREVIOUS WORKING DAY, THIS METHOD USES THE FRIDAY IF YESTERDAY WAS A WEEKEND*/
/*COLLECT TODAYS DATE (END_DATE) ASWELL SO THAT IF IT IS A WEEKEND THE TXNS PULLED WILL BE FOR THE WHOLE WEEKEND*/
DATA _NULL_;
    PreviousBusinessday = intnx('weekday',today(),-1) ;
 END_DATE=intnx('Day', today(), 0,'beginning');


CALL SYMPUT ('PreviousBusinessday',""|| PUT(PreviousBusinessday,DATE9.));
CALL SYMPUT ('END_DATE',""|| PUT(END_DATE,DATE9.));

RUN;

 

PROC SQL;

/*ENTER PROD PATH USER AND PASSWORD DETAILS BELOW*/

CONNECT TO ORACLE (PATH="XXXX" USER=XXXX password="&XXXpw.");
CREATE TABLE US_txns AS select * from connection to oracle

(SELECT DISTINCT X.*
FROM DB_US.TXNS X
WHERE
/*SEARCH FOR NAMES */
UPPER(TRIM(X.ORIG_NM)) LIKE '%ABC LIMITED%' OR
UPPER(TRIM(X.BENEF_NM)) LIKE '%ABC LIMITED%'

/* %str(%') required when using macro in Oracle as it does not accept double quotes. All strings and dates need to be wrapped in single quotes. */
AND X.TRXN_EXCTN_DT >= %str(%')&PreviousBusinessday.%str(%')
AND X.TRXN_EXCTN_DT < %str(%')&END_DATE.%str(%')

/*>= TO_DATE(%nrbquote(')&PreviousBusinessday%nrbquote('), 'YYYYMMDD')*/

);
DISCONNECT FROM ORACLE;
QUIT;


Accepted Solutions
Solution
‎05-12-2017 04:57 AM
Super User
Posts: 3,106

Re: Oracle Dates filter not working with a SAS to Oracle Passthrough

Maybe your ANDs and OR are getting confused. I would try this:

 

WHERE
/*SEARCH FOR NAMES */
(UPPER(TRIM(X.ORIG_NM)) LIKE '%ABC LIMITED%' OR
UPPER(TRIM(X.BENEF_NM)) LIKE '%ABC LIMITED%')
/* %str(%') required when using macro in Oracle as it does not accept double quotes. All strings and dates need to be wrapped in single quotes. */
AND X.TRXN_EXCTN_DT >= %str(%')&PreviousBusinessday.%str(%')
AND X.TRXN_EXCTN_DT < %str(%')&END_DATE.%str(%')
/*>= TO_DATE(%nrbquote(')&PreviousBusinessday%nrbquote('), 'YYYYMMDD')*/

View solution in original post


All Replies
Solution
‎05-12-2017 04:57 AM
Super User
Posts: 3,106

Re: Oracle Dates filter not working with a SAS to Oracle Passthrough

Maybe your ANDs and OR are getting confused. I would try this:

 

WHERE
/*SEARCH FOR NAMES */
(UPPER(TRIM(X.ORIG_NM)) LIKE '%ABC LIMITED%' OR
UPPER(TRIM(X.BENEF_NM)) LIKE '%ABC LIMITED%')
/* %str(%') required when using macro in Oracle as it does not accept double quotes. All strings and dates need to be wrapped in single quotes. */
AND X.TRXN_EXCTN_DT >= %str(%')&PreviousBusinessday.%str(%')
AND X.TRXN_EXCTN_DT < %str(%')&END_DATE.%str(%')
/*>= TO_DATE(%nrbquote(')&PreviousBusinessday%nrbquote('), 'YYYYMMDD')*/
Contributor
Posts: 26

Re: Oracle Dates filter not working with a SAS to Oracle Passthrough

Hi SAS Kiwi,

 

typically, as i posted the issue i noticed the absence of the brackets!

 

Fixed it straight away and its fine!

 

Thanks for the response anyway

Super User
Posts: 6,938

Re: Oracle Dates filter not working with a SAS to Oracle Passthrough

I guess that Oracle does not like the SAS date literals. Most RDBMS I know want date literals as "YYYY-MM-DD".

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 128 views
  • 0 likes
  • 3 in conversation