We’re smarter together. Learn from this collection of community knowledge and add your expertise.

First Date? Last Date? There’s an operator for each in SAS Visual Analytics!

by SAS Employee BobbieWagoner on ‎06-26-2017 01:02 PM (553 Views)

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.

LastDate1.jpg

 

LastDate2.jpg

 

 

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:

LastDate4.jpg

  

 

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.

LastDate5.jpg

 

 

To create the report, I created several new data items, high-lighted in blue below:

 

LastDate6.jpg

 

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:

 

LastDate7.jpg

  

The four aggregated data items' expressions are shown below: Amount—First Day of Month

 

LastDate8.jpg

   

Amount—Last Day of Month

LastDate9.jpg

 

First Day of Month

 LastDate10.jpg

 

Last Day of Month

LastDate11.jpg

   

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.

 

LastDate11(2).jpg

 

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.

Contributors
Your turn
Sign In!

Want to write an article? Sign in with your profile.


Looking for the Ask the Expert series? Find it in its new home: communities.sas.com/askexpert.