(SQL?) Add a "total row" for each group by combination

[morglum]

Hi SAS communities,

I have a database that has the population by 110 ages, 50 states and 2 sexes, meaning my database had 110x50x2 rows.

I would like to create extra rows that would contain all the possible combinations for  "all ages combined ", "all states combied " and "both sexes"

The new database would have 111x 51 x3 rows.   Is there an easy way to do this?

Thanks!

[LinusH]

Storing total rows in a  database in not recommended. Have them calculated during report creation instead.

[morglum]

Hi Linus,

I am in the report creation phase, how would you proceed to generate all these lines?

I may be missing something obvious like a proc freq or tabulate.

I just want the output to be a sas7bdat file.

[morglum · Accepted solution]

Proc TABULATE is the answer.  I was so focussed on thinking up a proc sql way that I forgot the obvious:

proc tabulate data= pop out=tabulated;

class age  sex state year ;

var pop;

table (age all) * (sexe all) * (state all) * (year), weight=' ';

run;