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;
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;
Why do you want to avoid using trunc()?
Is it because indexes on the dates are not used?
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;
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.
>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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: