topic Re: Date Where Statement Syntax in SAS Programming

Date Where Statement Syntax

Cheesiepoof05 — Mon, 28 Aug 2023 18:28:16 GMT

I'm trying to limit some results to within a certain date range and struggling with the syntax of how the date should be entered in my WHERE statement. The field is "datetime" format at the source database with numeric precision of 23. Looks like this "2022-08-05 12:35:08.637". Any ideas with how the date should be entered in my where statement?

PROC SQL;

CREATE TABLE WORK.Results AS

SELECT

Fruit,

Date

FROM Database.Table

WHERE Date >= 2022-08-05 12:35:08.637 and <= 2022-08-06 12:35:08.637

;

QUIT;

Re: Date Where Statement Syntax

Tom — Mon, 28 Aug 2023 18:54:32 GMT

No idea what a numerical precision of 23 means with a datetime value.

Are those milliseconds in your example time points really significant? That seems a little bit too much to ask for.

To enter a datetime constant it has to be something the DATETIME informat can interpret. You then enclose it in quotes and adds the letters DT after the closing quote. That is a datetime literal in SAS.

Your WHERE clause also has to use valid syntax. You cannot have the AND operator next to the LESS THAN OR EQUAL TO operator.

Perhaps you just want to use the BETWEEN syntax.

WHERE Date between "05AUG2022:12:35:08.637"dt and "06AUG2022:12:35:08.637"dt

Note you can also use BETWEEN in a normal WHERE statements outside of PROC SQL. That way you can skip trying use PROC SQL and just use normal SAS code.

data Results;
  set Database.Table(Keep=fruit date);
  WHERE Date between "05AUG2022:12:35:08.637"dt and "06AUG2022:12:35:08.637"dt;
run;

Re: Date Where Statement Syntax

mkeintz — Mon, 28 Aug 2023 18:52:17 GMT

Apparently your DATE variable takes datetime values (i.e. number of seconds after Midnight at the start of Jan 1, 1960). So if you want to express a constant for that variable you have to use the datetime-literal format.

I.e. your where clause should be

  where date >= '05aug2022:12:35:08.637'dt
  and
        date <= '06aug2022:12:35:08.637'dt;

Re: Date Where Statement Syntax

Cheesiepoof05 — Mon, 28 Aug 2023 19:07:32 GMT

I should have included this in my original question, but I don't care about the time portion of it. I would prefer to just use the date portion in my where statement and capture all the various times within that date. Is that possible?

Re: Date Where Statement Syntax

PaigeMiller — Mon, 28 Aug 2023 19:11:00 GMT

If variable named DATE is actually a SAS date value, and not (as we assumed) a SAS date/time value, then yes of course you can do this.

But they key question is about whether or not the variable named DATE is a date value or a date/time value. Which is it?

Re: Date Where Statement Syntax

Tom — Mon, 28 Aug 2023 19:14:28 GMT

@Cheesiepoof05 wrote:

I should have included this in my original question, but I don't care about the time portion of it. I would prefer to just use the date portion in my where statement and capture all the various times within that date. Is that possible?

In that case you could use the DATEPART() function and date literals.

where datepart(date) between '05AUG2022'd and "06AUG2022"d

But if the source is actually some external database then for performance reasons you might need to still need to use datettime values. Otherwise SAS might try to pull the whole database over to SAS so it can use the DATEPART() function.

where '05AUG2022:00:00'dt <= date < "07AUG2022:00:00"dt

Notice how I used midnight on the next day for the upper bound and use < instead of <= for the comparison test. That will make sure that timestamps that are milliseconds before midnight are included.

Re: Date Where Statement Syntax

Cheesiepoof05 — Mon, 28 Aug 2023 19:28:50 GMT

It is a date/time value, not just a date value.

I tried both methodologies and timed the return time. They both worked and returned the same results but the first methodology returned in half the time.

1 - where DATE between '05JAN2023 00:00:00'dt and '05MAY2023 23:59:59'dt

2 - where datepart(DATE) between '05JAN2023'd and '05MAY2023'd

I appreciate all of the help everyone has provided! As I'm still very new to SAS (and obviously using more SQL rather than SAS), anyone have a recommendation on a good learning course for beginners to build up my skillset?