Help using Base SAS procedures

Why isn't this query returning any records?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Why isn't this query returning any records?

Hello,

I have a table which contains multiple columns, one of the columns is call Date_Of and it contains dates in the following format. ddMONyyy:hh:mm:ss Ex. 26MAY2015:00:00:00.

I'm trying to use a query in which I try to extract only records of today.

   SELECT t12.DATE_OF,

      FROM QUERYt12

      WHERE t1.DATE_OF<=today();

QUIT;

Unfortunately the query doesn't extract any records, on the other hand if I switch the < operator to > it extracts all the data.

I think it's related to the format of the date, I could't find a solution to this problem, hope someone will be able to help me out here.

Appreciate your help.

Thank you


Accepted Solutions
Solution
‎05-26-2015 10:19 AM
Super User
Super User
Posts: 7,955

Re: Why isn't this query returning any records?

Posted in reply to MustafaAbdelhaq

Assuming t1.DATE_OF is a proper numeric data time value, then it includes date and time.  Today() function returns a date value, i.e. no time.

If you only want to compare dates then:

where datepart(T1.DATE_OF) <= today();

If you want time as well then you need to build a datetime response:

where T1.DATE_OF) <= dhms(today(),hour(time()),minute(time),0);

View solution in original post


All Replies
Solution
‎05-26-2015 10:19 AM
Super User
Super User
Posts: 7,955

Re: Why isn't this query returning any records?

Posted in reply to MustafaAbdelhaq

Assuming t1.DATE_OF is a proper numeric data time value, then it includes date and time.  Today() function returns a date value, i.e. no time.

If you only want to compare dates then:

where datepart(T1.DATE_OF) <= today();

If you want time as well then you need to build a datetime response:

where T1.DATE_OF) <= dhms(today(),hour(time()),minute(time),0);

Occasional Contributor
Posts: 8

Re: Why isn't this query returning any records?

It worked perfectly, thank you so much.

Super User
Posts: 11,343

Re: Why isn't this query returning any records?

Posted in reply to MustafaAbdelhaq

As to the why: Today() returns a number of days since 1 Jan 1960 as are all SAS DATE valued variables. If your variable actually contains a DATETIME value as shown then the value stored is the number of SECONDS.

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 235 views
  • 1 like
  • 3 in conversation