SAS Function that can create every possible combination

Question

I have a dataset that looks like this.

data test;
   input cat1 $ cat2 $ score;
   datalines;
A D 1
A D 2
A E 3
A E 4
A F 4
B D 3
B D 2
B E 6 
B E 5
B F 6
C D 8
C D 5
C E 4
C E 12
C E 2
C F 7
;
run;

I want to create tables based off of this table that are summarized forms of this data. For example, I want one table that sums every score for every cat1 and cat2 together, like so

proc sql;
create table all as select
    'all' as cat1
    ,'all' as cat2
    ,sum(score) as score
from test
group by 1,2
;quit;

I want a table that sums all the scores for cat1='A', despite what cat2 is, like so

proc sql;
create table a_all as select
    cat1
    ,'all' as cat2
    ,sum(score) as score
from test
where
    cat1='A'
group by 1,2
;quit;

I want a table that sums the score for cat1='A' and cat2='E', like so

proc sql;
create table a_e as select
    cat1
    ,cat2
    ,sum(score) as score
from test
where
    cat1='A'
    and
    cat2='E'
group by 1,2
;quit;

And so on and so forth. I want a comprehensive set of tables that consists of every possible combination. I can use loops if they are efficient. The problem that the real data set I'm using has 8 categories (as opposed to the 2 here) and within those categories, there are as many as 98 levels. So the loops I've been writing have been nested 8 degrees and take up a ton of time. Pain to debug too.

Is there some kind of function or a special array I can apply that will create this series of tables I'm talking about? Thanks!

Answer 1

I think you want what PROC SUMMARY does by default.

data test;
   input cat1 $ cat2 $ score;
   datalines;
A D 1
A D 2
A E 3
A E 4
A F 4
B D 3
B D 2
B E 6 
B E 5
B F 6
C D 8
C D 5
C E 4
C E 12
C E 2
C F 7
;
run;
proc print;
   run;
proc summary data=test chartype;
   class cat:;
   output out=summary sum(score)=;
   run;
proc print;
   run;