3 FAQs on date calculations in SAS Visual Analytics
Here at SAS, we often get questions about how to manipulate date and date/time variables in SAS Visual Analytics. Working with dates may seem tricky, but it doesn’t have to be if you keep in mind some simple functions that are available in SAS Visual Analytics.
Below I've summarized the most frequently asked questions we get about dates and provided steps on how to get answers to these. They may seem basic, but can go a long way when you’re working on reports that require date manipulation. For some more advanced calculations using parameters, check out Renato’s post on Date Parameters in SAS Visual Analytics.
1) Is there an easy way to figure out the difference in X between a date and the previous time period?
Yes! SAS Visual Analytics can automatically calculate the difference in a measure between a date and the previous time period, or, between a date and the parallel time period for a larger interval. For example, the difference in measure X between January and February of the same year, or, January of year Y and January of year (Y – 1) can be calculated by doing the following:
Right click on the measure you’re interested in finding the difference for and select Create. Then, simply select the difference you’re looking for based on the date you want to use.
2) How do I calculate the number of days between two time periods?
It’s simple. In the Report Designer, create a new calculated item. Name your calculated item and drag over the x-y function under Numeric (simple). Next, drag over the TreatAs. You can find this under Numeric (advanced). You can use this function when you have a datetime value and want SAS Visual Analytics to treat it as a different data type. In this case, a numeric one, as shown below. Drag over your start and end dates and save the calculated item. You can now use it in report objects such as a list table to easily find out how many days you have between two dates.
To find out the difference between today’s date and another date in your data, drag over the DatePart function, which can be found under Date and Time. Assign the Now function to it, which can also be found under the Date and Time section. Since the output of this is a date, you will have to use TreatAs again to wrap around DatePart (Now). Then, simply subtract TreatAs (your target date) with number as the output. To clean up the calculated item, select Numeric as its format once you’ve created it. Here’s what all of this looks like:
3) How can I allow the report consumer to pick a historical date and have my data filtered accordingly?
You already know that sliders work well for time ranges, but if your report user wants to see historical data for a particular date that falls into a start date and end date range, you can allow them to input this date in a Text Input box. You can take advantage of the Parse function here to filter the dates based on the user’s input.
First, create a new parameter called Choose Date and assign that as the parameter for your Text Input box. Then, drag over the Parse function (under Text (simple)), which will interpret the input string as ANYDTDTE9. if you set it to that format. You can then use BetweenInclusive, which is a Comparison operator to select the start and end dates variables based on your data. Note that for the Parse and BetweenInclusive operators to work together, they both need a date format. Here is what this filter looks like:
Once you have the filter set up, you can now test it by typing in a date in the Text Input box. If you create a list table with a few chosen variables, including the dates, of course, you will be able to see the list table filtered accordingly.
There are many more variations of these calculated items available. These are just the basics to help you get started with date calculations. Remember that TreatAs, Parse, the Comparison operators, and the DatePart operator are all great ones to play around with when you’re working with dates in SAS Visual Analytics.
What other tricks and tips for dates have you used?
