topic Re: How do I translate sql to_char to sas code? in SAS Procedures

How do I translate sql to_char to sas code?

tbagger70 — Thu, 26 Dec 2013 19:31:18 GMT

I'm trying to compare a datefield in an oracle table. Because of the field format, I have not been able to figure out a way to compare. I could really use some help.

The field data looks like this (named = Activity_attempt_dttm);

11JUN2008:16:06:09.111000

12JUN2008:13:54:18.749000

This is the basic sql I’m trying to use;

SELECT login_user_id, activity_attempt_dttm, audit_remark_txt

FROM MAPTAB.SESA_SECURITY_AUDIT

WHERE activity_attempt_dttm = "12JUN2008:13:54:18.749000";

The final solution for me is to be able to select data between a from date and a to date. I only need to compare the dates, not the time.

Any help would be greatful.

tbagger70

Re: How do I translate sql to_char to sas code?

DBailey — Thu, 26 Dec 2013 19:36:56 GMT

Most SAS installations accessing oracle dates use a SAS datetime variable and the sas connector translates between SAS and Oracle requirements. So..for a column that is defined as a date in Oracle you would simply use a datetime SAS variable:

SELECT login_user_id, activity_attempt_dttm, audit_remark_txt

FROM MAPTAB.SESA_SECURITY_AUDIT

WHERE activity_attempt_dttm = "12JUN2008:13:54:18.749000"dt;

SELECT login_user_id, activity_attempt_dttm, audit_remark_txt

FROM MAPTAB.SESA_SECURITY_AUDIT

WHERE activity_attempt_dttm GE'01Jun2008:0:0'dt

and activity_attempt_dttm LT '01Jul2008:0:0'dt;

Re: How do I translate sql to_char to sas code?

tbagger70 — Thu, 26 Dec 2013 19:51:35 GMT

Thanks for the help dbailey. Saved me

Re: How do I translate sql to_char to sas code?

NaveenSrinivasan — Thu, 26 Dec 2013 19:53:58 GMT

Hi,

Since you have mentioned that you want to select and filter only the Date and exclude the time, I believe you would need to apply the DATEPART function to extract the date and exclude the time, and then apply the filter.

Thanks,

Naveen

Re: How do I translate sql to_char to sas code?

DBailey — Thu, 26 Dec 2013 21:05:34 GMT

You can do that..but if you do SAS won't be able to pass the query to Oracle for execution. SAS would then pull all of the necessary information down locally to decide if a row met the datepart criteria.