topic Re: Sum total row in SAS Procedures

Sum total row

Krysia24 — Wed, 20 Jan 2021 23:55:17 GMT

I would like to add a total row to my table. Here is what I have:

Gender	Freq	Percent
Males	20	25%
Females	60	75%

Here is what I'd like:

Gender	Freq	Percent
Males	20	25%
Females	60	75%
Total	80

Just want the total there as its own row. What's the best proc to do this, as I realize you can do this a number of ways (tabulate, sql, etc.)

Thanks!

Re: Sum total row

Reeza — Thu, 21 Jan 2021 00:02:45 GMT

FREQ with WEIGHT or FREQ statements as you'll also get the percentages automatically.
PROC PRINT will also do that...

The best proc depends on what you're doing next and why you're doing this in the first place.

Re: Sum total row

novinosrin — Thu, 21 Jan 2021 00:04:00 GMT


data have;
input Gender $ 	Freq 	Percent :percent.;
format percent percent.;
cards;
Males	20	25%
Females 	60	75%
;

proc sql;
 select * from have
 union all
 select 'Total', sum(freq),. from have;
quit;
/*or*/
proc report data=have;
 column gender freq percent;
 define freq/analysis;
 compute after;
  line 'Total' +2 freq.sum ;
 endcomp;
run;

Re: Sum total row

Cynthia_sas — Thu, 21 Jan 2021 01:43:28 GMT

Hi:

Or, in the interest of completeness -- without a LINE statement and using an RBREAK statement, you can generate the report either with or without a total for percent:

Cynthia

Re: Sum total row

novinosrin — Thu, 21 Jan 2021 01:46:46 GMT

Yes thought of it. Just seemed straight forward enough to use the computed memory value by the engine soon as a variable was defined analysis. Point taken. Thank you 🙂

Re: Sum total row

Ksharp — Thu, 21 Jan 2021 11:32:57 GMT

data have;
 set sashelp.class;
run;

proc sql;
select sex,count(*) as n,calculated n/(select count(*) from have) as percent format=percent8.2 
 from have
  group by sex
union all
select 'Total' ,(select count(*) from have),. from have(obs=1);
quit;

Re: Sum total row

Cynthia_sas — Thu, 21 Jan 2021 15:30:12 GMT

Yes, it is straightforward, but the LINE statement may not align with the cells above as shown in the original pix of the desired output. The LINE statement output spans the whole table, and by default, the output from LINE is usually centered, so Total, would not always line up under Gender. So that's the side effect of RBREAK because RBREAK maintains the same number of cells on the break line.
Cynthia

Re: Sum total row

novinosrin — Thu, 21 Jan 2021 17:46:22 GMT

Yes that makes sense. I agree! 🙂 Thank you!

Also , @Cynthia_sas If you happen to know Jane Eslinger, please pass on my regards and gratitude to her for having authored that lovely proc report book. Kudos!!! I would say, a must read.

Re: Sum total row

Cynthia_sas — Thu, 21 Jan 2021 20:20:40 GMT

You're right - -it is a great book. I'll pass on your praise to her.
Cynthia