BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MR_E
Obsidian | Level 7

 

 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;

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

3 REPLIES 3
SASKiwi
PROC Star

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')*/
MR_E
Obsidian | Level 7

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 1444 views
  • 0 likes
  • 3 in conversation