DATA Step, Macro, Functions and more

SAS not using Truncate function but Oracle showing Truncate funcation

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

SAS not using Truncate function but Oracle showing Truncate funcation

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;


Accepted Solutions
Solution
4 weeks ago
PROC Star
Posts: 2,304

Re: SAS not using Truncate function but Oracle showing Truncate funcation

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;

 

View solution in original post


All Replies
Super User
Posts: 23,224

Re: SAS not using Truncate function but Oracle showing Truncate funcation

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
Contributor
Posts: 60

Re: SAS not using Truncate function but Oracle showing Truncate funcation

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

Re: SAS not using Truncate function but Oracle showing Truncate funcation

DBA still see the truncate function your suggestion didn't work
PROC Star
Posts: 2,304

Re: SAS not using Truncate function but Oracle showing Truncate funcation

Why do you want to avoid using trunc()? 

Is it because indexes on the dates are not used?

Contributor
Posts: 60

Re: SAS not using Truncate function but Oracle showing Truncate funcation

it's taking a lot of time to run the query.
Solution
4 weeks ago
PROC Star
Posts: 2,304

Re: SAS not using Truncate function but Oracle showing Truncate funcation

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;

 

Respected Advisor
Posts: 4,665

Re: SAS not using Truncate function but Oracle showing Truncate funcation

@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.

PROC Star
Posts: 2,304

Re: SAS not using Truncate function but Oracle showing Truncate funcation

@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.

 

 

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 177 views
  • 0 likes
  • 4 in conversation