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

[phoenix31]

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.

[Kurt_Bremser]

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

[phoenix31]

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.

[RW9]

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")

[phoenix31 · Accepted solution]

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")

[r_behata]

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;

[SuryaKiran]

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.