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,311 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.







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.