DATA Step, Macro, Functions and more

Proc SQL problem

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Proc SQL problem

Hi,

    I'm running below PROC SQL with range of dates in where clause giving issue.

   I'm trying to pull this data from oracle DB.

  

(t3.FILE_DATE_OF_REPORT BETWEEN input("&Start_Date",date9.) AND input("&End_Date",date9.)

               AND t2.SENDER_NAME LIKE 'SDI %'))

Somebody help me what was issue?

Thanks,

rk.


Accepted Solutions
Solution
‎08-26-2011 07:49 AM
Frequent Contributor
Frequent Contributor
Posts: 94

Proc SQL problem

It's probably better in the later versions of SAS, but certainly in 9.1.3 this could cause difficulties as SAS would end up processing the entire table as Oracle won't recognise the meaning of datepart.

I would normally try to send it datetimes instead, which should also allow it to use any indexes etc. on the table.  For example change your query to:

BETWEEN input("&Start_Date.:00:00:00",datetime20.) AND input("&End_Date.:23:59:59",datetime20.)

This article has some interesting notes on this: http://www.sascommunity.org/wiki/SAS_with_Oracle:_Writing_Efficient_and_Accurate_SQL

View solution in original post


All Replies
PROC Star
Posts: 7,356

Proc SQL problem

Oracle dates, I believe, are actually datetime fields.  Take a look at: http://www.nesug.org/proceedings/nesug04/io/io04.pdf

Super User
Posts: 9,662

Re: Proc SQL problem

I am not sure whether it wolud be work .just a guess.

Assuming FILE_DATE_OF_REPORT is datetime. format.

(datepart(t3.FILE_DATE_OF_REPORT) BETWEEN  "&Start_Date"d  AND "&End_Date"d)

               AND t2.SENDER_NAME LIKE 'SDI %'))

Ksharp

Solution
‎08-26-2011 07:49 AM
Frequent Contributor
Frequent Contributor
Posts: 94

Proc SQL problem

It's probably better in the later versions of SAS, but certainly in 9.1.3 this could cause difficulties as SAS would end up processing the entire table as Oracle won't recognise the meaning of datepart.

I would normally try to send it datetimes instead, which should also allow it to use any indexes etc. on the table.  For example change your query to:

BETWEEN input("&Start_Date.:00:00:00",datetime20.) AND input("&End_Date.:23:59:59",datetime20.)

This article has some interesting notes on this: http://www.sascommunity.org/wiki/SAS_with_Oracle:_Writing_Efficient_and_Accurate_SQL

Contributor
Posts: 35

Proc SQL problem

Hi DF,

If i hardcode date like datetime format it's working....is it gonna work with your logic.

It's a very big query....taking two hours to pull the data....so i would like to confirm before running.

WHERE (t1.RECEIVED_FILE_ID = t3.RECEIVED_FILE_ID AND t2.FILE_SENDER_ID = t3.FILE_SENDER_ID AND

      t1.CLD_PATIENT_COOKED_ID = t4.CLD_PATIENT_COOKED_ID AND t1.CLD_PATIENT_COOKED_ID =t5.CLD_PATIENT_COOKED_ID)AND

      ('1Jan2010:0:0:0'dt <= t3.FILE_DATE_OF_REPORT <= '25Aug2011:0:0:0'dt

       AND t2.SENDER_NAME LIKE 'SDI %'))

Thanks,

rk.

 

Trusted Advisor
Posts: 1,300

Proc SQL problem

using the ''dt will convert your text date to a sas numeric date value which may not correctly represent the same date in your oracle machine.

%let startdate=01jan2010;

%let enddate=31dec2010;

data _null_;

call symput('start', "'" || "&startdate.:00:00:00" || "'");

call symput('end', "'" || "&enddate.:00:00:00" || "'");

run;

proc sql;

/* stuff */

&start <= t3.file_date_of_report <= &end

/* otherstuff */

quit;

Contributor
Posts: 35

Proc SQL problem

Thanks all.....it worked.

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 244 views
  • 0 likes
  • 5 in conversation