BookmarkSubscribeRSS Feed
Pyrite | Level 9

Hi all,


Not sure if this is possible, have had a look around but not found anything so thought I would ask.


We need to calculate population rollover year on year.


For example:

Year 1 population for 2016 = 100

Year 2 population for 2017 = 95


Rollover = 95/100 ( 5 dropped out)


i have set up an index column which is combination of calendar year and program year, this looks like 1701, 1702, 1703, 1801, 1802, 1803.


My data table looks like


Index - Population

1601 - 100

1602 - 95

1603 - 92

1604 - 80

1701 - 110

1702 - 108

1703 - 101

1704 - 99



In our other BI tool I can just create a formula that uses the index column - 101 to reference the previous year then dynamically calculate the ratio. The only approach I can work out in VA is to pre-calculate before import which limits flexibility. Is there a way I could attack this?


Any ideas appreciated

SAS Employee

If you are in Designer, you can right-click on the population variable and select 'create'; one of the options should be new difference from previous period, percent difference from previous period, etc.  I believe these are dependent on having date items in the data to make the aggregation well-defined, so you might need to import the year as its own column or created a calculated date column from your Year/Program Year column.


A similar calculation is available in Explorer.

Pyrite | Level 9

Thanks Mike,


The issue with that approach is it lets me go back a calendar year but not a programme year at the same time,


For example population in year 1 2016 becomes year 2 2017


This images show how the populations progress:



SAS Employee

I see; I think to achieve what you want in VA you might have to separate each program population into its own column.  If you don't want to do that on the raw data, you could try making a calculated item for each cohort that checks your combination year item for the program year.  If it matches, copy the population value to the calculated item.  If it doesn't match, set to 0 or missing.


Then, you can aggregate these calculated items by year, and use the period calculations I mentioned earlier.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 2 in conversation