You may run into the requirement where you need to build out a date to use in either:
Below is a quick reference for how you can build a date to use in your SAS Visual Analytics reports and a few example requirements that have popped up lately.
I have recorded an accompanying video tutorial for these examples so that you may follow along.
From the Data pane, use the New data item menu and select Calculated item.
Then, in the expression builder, expand the Date and Time grouping and add the DateFromMDY to the expression. Each parameter value is expected to be numeric representing month, day, year, hence the name MDY. For full details, see SAS Documentation under Reference: Operators for Data Expressions.
In this example below, DateFromMDY(1, 1, 2021), evaluates to January 1, 2021.
Here is this new calculated data item in a List Table object:
You can expand the data item properties and use the Format button to view all of the available formats.
Now let’s cover how to use this operator to build a dynamic date. Let’s imagine our requirement is to Filter an object to only show data for the current month and beyond. For this, we will need to build a date that represents the first day of the current month to use in the filter.
In pseudocode, we will need to create the syntax for: DateFromMDY( current month, 1, current year).
Here is what the expression looks like:
Let’s take a closer look at the current month expression. Remember that the expected parameter is to be numeric. The Month() operator returns a numeric 1-12 of a date parameter. The Now() operator returns a datetime value so we must wrap the DatePart() operator around it to extract only the date value. You can use the icon in front of the operators to help determine the operators’ result type.
Here are what some of the expression values look like when separated:
So when we want to use this dynamic calculation in a Filter, it would look something like this. We want to return the rows where the Day is greater than or equal to the first day of the current month.
You can also use dates in Display Rules. The date data item must be assigned a role in the object, so in my example I will be using a List Table object. Let’s say the requirement for this object is to dynamically highlight the rows for the current month only, but with an advanced twist.
If there were not advanced twist, we could do this by calculating two data items, if you don’t already have them in your data, for month. You could calculate the month of the row by Month(date) and current month as Month(Now()) and then use those data items in the Display Rule expression. If Month(date) = Month(Now()) then highlight the row.
However, to showcase the build a date example, let’s say that we want to highlight the rows of data from the previous month’s 15th to the current month’s 15th. So we will need to create two data items to represent the Previous Month (15th) and Current Month (15th).
The Display Rules does not have a Between operator we can use so we will need to stack two display rules together. We will create one rule where we will highlight all of the days less than the Current Month (15th) in blue and then remove the highlighting, by highlighting the rows white, i.e. the default table formatting, for the days less than the Previous Month (15th).
Here is what the theory looks like:
And here is what the application looks like. You can see that rows have been highlighted for the dates between July 15, 2021 though August 15, 2021.
Here is the expressions for Current Month (15th):
And here is the expression for the Previous Month (15th). Notice how we will have to account for the calendar rollover. If the current month is January then we will need to return December of the previous year. You can see in the screenshot below that you can perform addition or subtraction within the parameter expression since both Month() and Year() operators return numeric values.
You can easily create additional calculated data items to test the logic. I didn’t really need to test the December logic for the previous month but I did just incase I decided to do something with the next month logic.
You now have several examples of how to use a date data item that you constructed with the DateFromMDY(M,D,Y) operator and how to dynamically generate today’s date as well as previous or next values.
For additional date examples refer to one of the below references:
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.