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.
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.
@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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.