Sum and mean combined in SAS VA

[PaalNavestad]

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?

[acordes · Accepted solution]

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;

[PaalNavestad]

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.