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;
... View more