topic Re: Sum and mean combined in SAS VA in SAS Visual Analytics

Sum and mean combined in SAS VA

PaalNavestad — Wed, 22 Sep 2021 06:36:16 GMT

Hi all I have data that in two different hierarchies.

Week-date and department sub department.

Example data looks like this

Week Date Department Sub-department Value

1 01jan A A1 8

1 02jan A A1 9

1 03jan A A1 10

1 01jan A A2 2

1 02jan A A2 1

1 03jan A A2 0

I want to have a cross table that has the week date as rows , department sub-department as columns.

I want the average by week date and the sum of department sub-department

What I want for collapsed hiearchies

Week Department

1 10

What I get using mean for value

Week Department

1 5

What I get using sum for value

Week Department

1 30

When expanding department to sub-department I get the right mean when week-date is collapsed

I have tried several different combination of sum and mean, calculating the number of days and trying table aggregations.

I can solve this by creating an aggregation by week date- department and create a cross tab on this. Then I need to create two crosstabs, one for collapsed department and on for expanded department.

This is not really elegant. In the real use case I already have several aggregation and joins and really want to avoid to many.

I seem to remember that I had the same challenge in Proc Tabulate back in the late 90's and managed to solve this by a strange combination of crossings.

Is there anybody who has been able to solve this problem without using a separate aggregated table?

Re: Sum and mean combined in SAS VA

acordes — Wed, 22 Sep 2021 09:35:45 GMT

I don't understand what this aggregation scheme should accomplish, for me it's confusing to mix average and sum in combination with hierarchies.

nevertheless I think it can be done with the aggregated measure by building a nested container.

data public.test_com(promote=yes);
infile datalines;
format date $9.;
input Week  Date $    Department $ Sub_department $ Value;
datalines;
1          01jan2021  A                   A1                       8
1          02jan2021  A                   A1                       9
1          03jan2021  A                   A1                      10
1          01jan2021  A                   A2                       2
1          02jan2021  A                   A2                       1
1          03jan2021  A                   A2                       0
;
run;

Re: Sum and mean combined in SAS VA

PaalNavestad — Wed, 22 Sep 2021 13:11:22 GMT

Thanks a lot. Yes it is confusing mixing sum and mean. However the data is daily averages so you want an average by date and a sum of the departments. This creates a lot of havoc in a lot of reporting and analysis. Most of the use cases is by day or by month. Then you can use sum of month and calculate daily by dividing by days in month. However this do not work in this case as the data is updated daily and you will never know if you have complete months.