topic Re: Proc SQL Filter between previous Business day and current date in the where clause in SAS Programming

Proc SQL Filter between previous Business day and current date in the where clause

phoenix31 — Mon, 12 Nov 2018 15:05:11 GMT

Hello All,

I have an Oracle query which pulls in data from the previous Business day however I am trying to convert this to a SAS query (Proc SQL Statement) and need help converting previous day Business day (M-F) in SAS.

Oracle code:

select * from tablename where columnname BETWEEN decode(to_char(sysdate,'D'),2,trunc(sysdate-3),trunc(sysdate-1)) AND trunc(sysdate)

Can anyone help me with this?

Please let me know if more information is needed.

Re: Proc SQL Filter between previous Business day and current date in the where clause

Kurt_Bremser — Mon, 12 Nov 2018 15:14:30 GMT

In SAS, apply the weekday() function to a date. It will return values from 1 to 7 (1 = Sunday, 7 = Saturday).

Re: Proc SQL Filter between previous Business day and current date in the where clause

RW9 — Mon, 12 Nov 2018 15:17:43 GMT

Use intnx function and today():

decode(to_char(sysdate,'D'),2,trunc(sysdate-3),trunc(sysdate-1)) AND  trunc(sysdate)

Something like:

between intnx('month',today(),0,"b") and intnx('month',today(),1,"b")

Re: Proc SQL Filter between previous Business day and current date in the where clause

r_behata — Mon, 12 Nov 2018 15:44:54 GMT

You may also consider using SQL Pass-thru for Oracle. That way you can leverage your existing SQL query to get the same result from within sas.

Assuming that the SAS/Access to Oracle is licensed :

PROC SQL;    
  CONNECT TO ORACLE (user=user password="password" path=schema);
  CREATE TABLE LIBRARY.tab_nm AS
   SELECT *       
      FROM CONNECTION TO ORACLE          
       (
           <Your query here>
     	);
  DISCONNECT
  FROM ORACLE; 
 QUIT;

Re: Proc SQL Filter between previous Business day and current date in the where clause

SuryaKiran — Mon, 12 Nov 2018 15:58:14 GMT

You may need to consider running an explicit pass-through instead, if your table is very large. When you change it to SAS Query and use some SAS function then those functions may not be converted to Oracle specific by SAS.

In your case your trying to use equivalent SAS functions in where clause, if SAS cannot convert them all of the data without the where clause will be brought to SAS Server and where clause will be applied on SAS side instead of Oracle side.

proc sql;
connect to Oracle (user= password= server=);
create table SAS_Table as 
select * 
from connection to Oracle 
(
select * from tablename where columnname  BETWEEN  decode(to_char(sysdate,'D'),2,trunc(sysdate-3),trunc(sysdate-1)) AND  trunc(sysdate)

);
quit;

Check this document to see what formats will be applied by default for the data types in Oracle.

Re: Proc SQL Filter between previous Business day and current date in the where clause

phoenix31 — Mon, 12 Nov 2018 15:57:52 GMT

Thank you.
I used the below to get the desired result:

intnx('WEEKDAY',today(),0,"b") and intnx('WEEKDAY',today(),-1,"b")

To get only the prior business day data.

Re: Proc SQL Filter between previous Business day and current date in the where clause

phoenix31 — Mon, 12 Nov 2018 15:59:14 GMT

Thank you so much!

I used your query and updated month to weekday to get business days only.

Code:

between intnx('WEEKDAY',today(),0,"b") and intnx('WEEKDAY',today(),-1,"b")