topic Re: Calculate Date Range in SAS Programming

Calculate Date Range

sharmams0978 — Wed, 26 May 2021 03:54:28 GMT

Hello,

I have a datetime column that has values such as shown below as a sample

04MAY2018:08:46:20.316999912

11JAN2021:11:07:37.048000097

17MAR2019:00:24:55.558000088

how do I filter the whole table based on the above column date range say between 04MAY2018 AND 17MAR2019? I tried this: WHERE DATE BETWEEN "04MAY2018"d AND "17MAr2019"d but it doesn't work. Please help.. thank you.

Re: Calculate Date Range

jimbarbour — Wed, 26 May 2021 05:06:00 GMT

@sharmams0978 wrote:

Hello,

I have a datetime column that has values such as shown below as a sample

04MAY2018:08:46:20.316999912

11JAN2021:11:07:37.048000097

17MAR2019:00:24:55.558000088

how do I filter the whole table based on the above column date range say between 04MAY2018 AND 17MAR2019? I tried this: WHERE DATE BETWEEN "04MAY2018"d AND "17MAr2019"d but it doesn't work. Please help.. thank you.

Well, you can't compare a Date-Time variable with a Date value. Date-Time variables are much larger, and it will not be a valid comparison. If you want to use a date range, the DATEPART function should do the trick. Something like this:

DATA Filtered_Data;
    SET Unfiltered_Data;

     IF  '04MAY2018'd < DATEPART(Datetime_Column) < '17MAR2019'd;
RUN;

Jim

Re: Calculate Date Range

s_lassen — Wed, 26 May 2021 08:24:07 GMT

The problem is that your values are datetimes, not dates. Unlike e.g. T-SQL, where you can compare dates and datetimes directly, you will either have to convert the datetimes in the table to dates (using the DATEPART function, as suggested by @jimbarbour ) or use datetime constants in your comparison. I would go with the last solution, as it executes faster (datepart does an extra calculation for every single row in the table):

proc sql;
  select * from have where
  datetime_column>='04may2018:00:00:00'dt and
  datetime_column<'18mar2019:00:00:00'dt
;

Note that I changed the BETWEEN construct, in order to get exactly the same result as you would with BETWEEN on the dateparts and the dates you mentioned.