BookmarkSubscribeRSS Feed
kovalzdw
Calcite | Level 5

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.

IDDATE_FORMDATE_TO
101.01.202301.02.2023
215.01.202310.05.2023
301.02.202301.03.2023
415.02.202315.03.2023
510.04.202310.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

 

 

5 REPLIES 5
Tom
Super User Tom
Super User

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
Calcite | Level 5
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
PROC Star

@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 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Sam_SAS
SAS Employee

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

 

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

Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1221 views
  • 0 likes
  • 4 in conversation