Help using Base SAS procedures

How do I translate sql to_char to sas code?

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

How do I translate sql to_char to sas code?

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


Accepted Solutions
Solution
‎12-26-2013 02:36 PM
Super Contributor
Posts: 578

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

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;


or


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;




View solution in original post


All Replies
Solution
‎12-26-2013 02:36 PM
Super Contributor
Posts: 578

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

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;


or


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;




New Contributor
Posts: 3

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

Thanks  for the help dbailey. Saved me

Occasional Contributor
Posts: 18

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

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

Super Contributor
Posts: 578

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

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. 

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 845 views
  • 0 likes
  • 3 in conversation