Hello All,
I have numeric data on a count based on multiple criteria from specific date. The date and character variables are
(Date, Food Name, Compliments).
I need to calculate the change in bottles of Ketchup used for hamburgers and hotdogs from
Jan 1, 2014 to Sept 1, 2014 to Jan 1, 2015 to Sept 1, 2015.
So far I have attempted the following % difference features in SAS VA:
1. All periods to the percent change including the period with date, system fails to recognize these specific dates
2. The create percentage difference feature based on the numeric count, system fails
I attached the file to this posting. Have fun! and please let me know if I can provide more details.
Thank you, Robert
Hello @bobbyfajardo,
I might have a solution for you but it depends on the version you are using. In SAS VA8.3 a new operator was published. AggregateCells aggregates the values of a specific set of cells in the detail data table for an object and allows offset starting point for the aggregation. I have used that operator to basically calculate:
( AggregateCells(_Sum_, 'Bottles_Used'n, column, CellIndex(current, 0), CellIndex(current, 0)) - AggregateCells(_Sum_,'Bottles_Used'n, column, CellIndex(current, -1), CellIndex(current, -1)) ) / AggregateCells(_Sum_, 'Bottles_Used'n, column,CellIndex(current, -1), CellIndex(current, -1))
If you are running < VA8.3 then unfortunately I do not have an answer for you.
Best regards,
Petri
Hello @bobbyfajardo,
I might have a solution for you but it depends on the version you are using. In SAS VA8.3 a new operator was published. AggregateCells aggregates the values of a specific set of cells in the detail data table for an object and allows offset starting point for the aggregation. I have used that operator to basically calculate:
( AggregateCells(_Sum_, 'Bottles_Used'n, column, CellIndex(current, 0), CellIndex(current, 0)) - AggregateCells(_Sum_,'Bottles_Used'n, column, CellIndex(current, -1), CellIndex(current, -1)) ) / AggregateCells(_Sum_, 'Bottles_Used'n, column,CellIndex(current, -1), CellIndex(current, -1))
If you are running < VA8.3 then unfortunately I do not have an answer for you.
Best regards,
Petri
I'm in SAS VA 7.3. This looks really handy! When we upgrade, I'll give it a try. Today, we found a work around in SAS VA 7.3.
All other SAS users, let me know if you are interested in the solution.
Best,
Robert
Nice to hear that you found another way to get where you want 😃 Could I ask you to please share your approach here?
Best regards,
Petri
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.