Percentage difference between 3 specific dates per year

[bobbyfajardo]

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

[PetriRoine · Accepted solution]

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

[bobbyfajardo]

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

[PetriRoine]

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