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

Solved
Frequent Contributor
Posts: 96

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

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!

Accepted Solutions
Solution
‎12-16-2014 10:54 PM
Frequent Contributor
Posts: 96

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

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;

All Replies
Super User
Posts: 5,876

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

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

Data never sleeps
Frequent Contributor
Posts: 96

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

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.

Solution
‎12-16-2014 10:54 PM
Frequent Contributor
Posts: 96

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

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;

🔒 This topic is solved and locked.