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