topic Re: Creating a Sum total for each Group? in ODS and Base Reporting

Creating a Sum total for each Group?

mcook — Wed, 13 Oct 2021 17:18:00 GMT

I am trying to recreate a table with the following layout

The input dataset is simply the 3 columns, Grouping, Var1, and N1

etc.

How do i create the Total for each Group?

Re: Creating a Sum total for each Group?

mcook — Wed, 13 Oct 2021 17:20:07 GMT

Apologies, i forgot to specify. I am trying to accomplish this in ODS Tagsets.RTF Proc Report.

Re: Creating a Sum total for each Group?

PaigeMiller — Thu, 14 Oct 2021 12:31:11 GMT

Using data set SASHELP.CLASS, I can produce the requested report

proc sort data=sashelp.class out=class;
    by sex;
run;
data class;
    set class;
    by sex;
    if first.sex then index=0;
    index+1;
    sample=cats('Sample',index);
    drop index;
run;

/* Now I have a data set similar to yours */
/* PROC REPORT does the rest */
proc report data=class;
    columns sex sample height;
    define sex/group;
    define sample/display;
    define height/sum;
    break after sex/summarize;
    compute after sex;
        sample='Total';
        sex=' ';
    endcompute;
run;

Re: Creating a Sum total for each Group?

Ksharp — Thu, 14 Oct 2021 12:02:05 GMT

How about this one ?

ods Tagsets.RTF file='c:\temp\temp.rtf' style=journal;
proc sql;
create table have as
select sex,1 as id,Smoking_Status,sum(weight) as weight
 from sashelp.heart
  group by sex,Smoking_Status
union
select sex,2 as id,'Total',sum(weight) as weight
 from sashelp.heart
  group by sex
;
quit;


proc report data=have nowd spanrows missing
style(report)={rules=cols } 
style(header)={fontstyle=roman fontweight=bold background=verylightgrey borderbottomcolor=black borderbottomwidth=2px}  ;
    columns sex id Smoking_Status  weight;
    define sex/group style={just=c vjust=m  bordertopcolor=black bordertopwidth=2px};
 define id/group noprint;
    define Smoking_Status/group;
    define weight/display;
 compute Smoking_Status;
     if Smoking_Status='Total' then 
call define(_row_,'style','style={bordertopcolor=black bordertopwidth=2px borderbottomcolor=black borderbottomwidth=2px}');
 endcomp;
run;
ods Tagsets.RTF close;