How to simulate rolling date in SAS VA

[#1]

I’ve seen many requests on how to display a rolling date variable in VA. Here is one way to make that happen. In this case, I am going to make a rolling date period based on Year and Quarter. I want four of the most recent quarters. To do this, we will convert our datetime category into a numerical value which we will then use to rank a table.

Create a new date category that will show year and quarter

Because we want to rank a table to give only the last 4 quarters we will first create a new date category that contains the year and quarter.

From the Data tab, select your datetime category then right click and choose Duplicate Data Item.

Next we'll change the properties of our new data item. Click on your new data item, in the Properties section give your new data item a better name and make the format Year, Quarter.

Create a New Calculated Item

Next we'll create a new calculated item that will convert our new Year Quarter category to a number so that we can use it to rank by.

From the Data tab, select the dropdown menu and choose New Calculated Item.

Give the new calculated Item a name (in this case YearQuarterCalculation).

Next we'll drag and drop the Treatas operator located under Number (Advanced) to the Visual panel on the right.

Make sure the dropdown displays _Number_ then drag and drop the new Year Quarter date category onto the bottom box in the equation.

Your equation should look like this:

Click the Ok botton. 

Modify the Properties for Your New Calculated Item

Next, we’ll modify our newly calculated measure so that it displays the values we need in order to use it properly for ranking. 

Let’s look at the properties for this new calculated measure. From the left Data pane, select our new calculated item. You can see from the properties that we have Comma for format and Sum for aggregation. Let’s change these.

 

From the Format drop down, select Numeric, and for Aggregation, select Average.

Now, let’s look at the results in a table. Here we see our Year Quarter category and our new calculated measure. Please note, it doesn't make sense to display our YearQuarterCalculation in a report as it's a SAS based numerical representation of the date. However, we can now use this measure for ranking.

Rank Your Table so that Only the Last Four Quarters Show

From the Ranks tab for the table, select the Year Quarter category we want to rank. Then click the Add Rank button.

Select Top Count from the first drop down, then enter 4 in the box next to Count box. Make sure the By: has our calculated measure selected and then uncheck All Other if you wish.

Here is our final result:

We are now only showing the most recent 4 quarters. I hope you found this informative.

Melanie