First Date? Last Date? There’s an operator for each in SAS Visual Analytics!
I’ve written several blogs this year relating to displaying date information in SAS Visual Analytics reports. I recently found myself thinking of the First and Last aggregation operators in the Designer, and how they might be used to solve a couple of different date display problems.
The First and Last operators show up in the list of Aggregated (advanced) operators in the designer interface for creating a new aggregated measure.
Each of the operators require four parameters:
- Either _ByGroup_ or _ForAll_ , the typical operators that appear with aggregations
- A measure data item
- A sequence item; a date, time, datetime, or numeric data item that orders the rows of the source table.
- Either _IncludeMissing_ or _ExcludeMissing_ to indicate whether or not missing values should be included.
To illustrate the operators used with dates, I’ll use data that contains daily totaled dollar amounts. Every possible date of each month/year does not necessarily appear in the data, as you can observe in the first 24 rows, showing the values of date and amount:
The First and Last operators play an important part in displaying the table below: a row of data for each month and year, showing the amount values for both the first day of the month that appears in the data and the last day of the month that appears.
To create the report, I created several new data items, high-lighted in blue below:
MonthYear is a duplicate of Date with a the format changed to MMYYYY. Day of month is a calculated measure created by the following expression in visual and text modes:
The four aggregated data items' expressions are shown below: Amount—First Day of Month
Amount—Last Day of Month
First Day of Month
Last Day of Month
You could also create the report below showing the amounts for the first and last days of each year by simply duplicating and modifying the names of a few data items.
Amount—First Day of Month was duplicated and renamed to Amount—First Day of year.
Amount—Last Day of Month was duplicated and renamed to Amount—Last Day of Year.
The date data item was duplicated and renamed to Year, changing the format to Year, also.
Now you can use the three new data items to display the values for the first date in the year and the last date in the year. I’ll emphasize that the expressions for the new amounts did not change at all. The only other change was substituting the Year data item for the MonthYear data item in the list table, making the By-Group now take on the value of Year, rather than MonthYear.
This example is simply one application of the First and Last operators, and their use is certainly not limited to date information, but if you hadn't used the operators with dates, or hadn't used them at all, hopefully this example will give you a start and new ideas.
