SAS not using Truncate function but Oracle showing Truncate funcation

[Ahsan]

Hello,

I am just running this simple code but DBA were telling me that I am using truncate function but I am not using it. That's what DBA is seeing in Oracle Database WHERE  ( (TRUNC("PAID_DT") >=TO_DATE('01JAN2012','DDMONYYYY','NLS_DATE_LANGUAGE=American') ) AND  ( ( ( (TRUNC("SVC_BGN_DT") BETWEEN TO_DATE('01JAN2012','DDMONYYYY','NLS_DATE_LANGUAGE=American') AND TO_DATE('31DEC2017','DDMONYYYY','NLS_DATE_LANGUAGE=American') ) OR  (TRUNC("SVC_BGN_DT") BETWEEN TO_DATE('31DEC2017','DDMONYYYY','NLS_DATE_LANGUAGE=American') AND TO_DATE('01JAN2012','DDMONYYYY','NLS_DATE_LANGUAGE=American') ) ) ) ) 

What should I do in the code that oracle don't use the truncate function behind the scene. All the help will be appreciated.

PROC SQL;

CREATE TABLE work.Claims AS

SELECT t1.TRNSCT_CNTL_NBR,

 t1.LINE_NBR,

t1.MEDICAID_ID,

t1.BILL_PRVDR_NPI

FROM RADWPUB.Table

WHERE t1.PAID_DT >= '1Jan2012'd

AND t1.SVC_BGN_DT BETWEEN '1Jan2012'd AND '31Dec2017'd

AND t1.BILL_PRVDR_NPI = '1013918960'

AND t1.CR_CD = '0';

QUIT;

[Reeza]

Oracle stores dates as date times and you probably are using SAS dates, which only have the date part, so TRUNC is being used to convert the dates to the same type to allow for the comparisons. If you used a DateTime instead would it help?

where t1.paid_dt >= '01Jan2012:00:00:00'dt

[Ahsan]

I am running the query right now using your suggestion I will let you know shortly.

[Ahsan]

DBA still see the truncate function your suggestion didn't work

[ChrisNZ]

Why do you want to avoid using trunc()? 

Is it because indexes on the dates are not used?

[Ahsan]

it's taking a lot of time to run the query.

[ChrisNZ · Accepted solution]

1. Why is trunc() the reason for the long run time?

2. Post the new native sql generated by by the SAS datetime

3. You can use explicit pass through to run exactly the code you want, something like:

PROC SQL;
connect using RADWPUB;
CREATE TABLE work.Claims AS
select * from connection to RADWPUB (
 SELECT TRNSCT_CNTL_NBR,
 LINE_NBR,
 MEDICAID_ID,
 BILL_PRVDR_NPI 
 FROM Table
 WHERE PAID_DT >= to_date('1Jan2012')
 AND SVC_BGN_DT BETWEEN to_date('1Jan2012') AND 
 to_date('31Dec2017')
 AND BILL_PRVDR_NPI = '1013918960'
 AND CR_CD = '0'
);
QUIT;

[Patrick]

@Ahsan

It's most likely not Oracle but the SAS Access to Oracle engine which adds this truncate to the code.

You can see what SAS sends to Oracle for execution if you use the following options before your query:

OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;

The Oracle trunc() function won't change the data type. It simply aligns Oracle datetime values to the beginning of the day. I guess your SAS variable contains a SAS Date and not a SAS Datetime value. 

Not sure why the SAS Oracle engine is doing this. Your best bet for having full control over the Oracle SQL is to use explicit pass-through SQL. And as you apparently got Oracle DBA support this person might then even help you to performance tweak this Oracle SQL.

[ChrisNZ]

@Patrick 

>Not sure why the SAS Oracle engine is doing this. 

As the SAS value is a date and the Oracle value is a datetime, some translation must be made as some point, and trunc() is a clean and easy way to do this.

Now if this prevents indexes from being used, it becomes a ruinous solution, and the SAS Oracle engine should definitely opt for another way to match the values. If that's the case I would classify the decision to use trunc() as a non-critical performance defect.