Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 96
Accepted Solution

(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;

View solution in original post


All Replies
Super User
Posts: 5,257

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 314 views
  • 3 likes
  • 2 in conversation