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

How to simulate rolling date in SAS VA

by SAS Employee MelCar on ‎06-28-2016 02:04 PM - edited on ‎07-05-2016 02:39 PM by Community Manager (1,240 Views)

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.

 

Rolling Date 1a.png

 

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.

 

Rolling Date 2a.png

 

 

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.

 

Rolling Date 1.png

 

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

 

Rolling Date 2.png

 

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

 

Rolling Date 3a.png

 

 

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

 

Rolling Date 4a.png

 

Your equation should look like this:

 

Rolling Date 8a.png

 

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.

 

Rolling Date 17.png 

 

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

 

Rolling Date 18.png

 

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.

 

Rolling Date 6a.png

 

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.

 

Rolling Date 20.png

 

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.

 

Rolling Date 21.png

 

Here is our final result:

 

Rolling Date 7a.png

 

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

 

Melanie

 

         

 

 

Contributors
Your turn
Sign In!

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