How to filter date column with "greater than" parameter

[kovalzdw]

I have a problem with creating a report in VA. I have a table containing a series of data and dates in which they are valid. The report based on this table has filters with the date from which the data should be visible and the date to which the data should be visible. The problem I encountered is that I am unable to add a filter so that the data displayed in the listing table has a date greater than or equal to the date selected in the filter.

For example, the dataset looks like below.

ID	DATE_FORM	DATE_TO
1	01.01.2023	01.02.2023
2	15.01.2023	10.05.2023
3	01.02.2023	01.03.2023
4	15.02.2023	15.03.2023
5	10.04.2023	10.05.2023

With filters DateFrom="20.02.2023" and DateTo="16.03.2023", I would like to see only records with ID equal 2, 3, 4, because only for those IDs date filters are "inside" DATE_FROM and DATE_TO range. 

Hope I explained my problem clearly.

Regards, 

Maciej

[Tom]

Why are you using strings to store dates?  Store them as DATES instead and then you should be able to use inequality operators on the values.

If you do want to store them in strings theninstead of using DAY MONTH YEAR order like "15.03.2023" or MONTH DAY YEAR order like "03.15.2023" you should use YEAR MONTH DAY order like "2023.03.15".

Then the lexical comparisons will be in the same direction as the chronological comparisons.

[kovalzdw]

Table I attached to my question is just to show what data I have. Date values of course are stored as numerical with date format.

[mkeintz]

---

@kovalzdw wrote:
Table I attached to my question is just to show what data I have. Date values of course are stored as numerical with date format.

---

Given you have dates stored as "numerical with date format", you have to enter constant values as date literals  (analogous to "XXX" as a character literal and 38 as a numeric literal).  The syntax for a date literal is 'mmddyyyy'd - as in '04feb2021'd (you can also use double quotes). 

SAS also honors time literals, date-time literals, hexadecimal literals, and binary literals.

See SAS Constants in Expressions 

[Sam_SAS]

Yes, this should work. Try an advanced filter expression like this:

('DateFrom'n >= '20FEB2023'd) AND ('DateTo'n <= '16MAR2023'd)

[Tom]

This article seems to be about using CUSTOM code to filter by a date range.

https://communities.sas.com/t5/SAS-Communities-Library/SAS-Viya-Filter-Records-by-Date-Custom-Step-F...

Not sure if you need to do custom filtering of it there is a more out of the box date filtering that is not as complicated.

The article seems to be saying that the macro variables are created with the dates in YMD order. Not sure if it does that for all date fields or if it matches the date format used by the variable.  But you can see they had to add logic in the SAS code to convert the strings put in the macro variable back into dates.