BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PaalNavestad
Pyrite | Level 9

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?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
acordes
Rhodochrosite | Level 12

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;

 

cross tabs.pngaggr.png

View solution in original post

2 REPLIES 2
acordes
Rhodochrosite | Level 12

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;

 

cross tabs.pngaggr.png

PaalNavestad
Pyrite | Level 9

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.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 2 replies
  • 967 views
  • 1 like
  • 2 in conversation