topic (SQL?) Add a "total row" for each group by combination in SAS Procedures

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

morglum — Wed, 17 Dec 2014 02:49:29 GMT

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 — Wed, 17 Dec 2014 03:01:16 GMT

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

morglum — Wed, 17 Dec 2014 03:34:08 GMT

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 — Wed, 17 Dec 2014 03:54:48 GMT

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;