topic Re: Grand total in proc report not working in SAS Procedures

Grand total in proc report not working

Q1983 — Tue, 16 Sep 2014 14:32:58 GMT

proc sql;

create table Trials_Final_cnt as

select type,past_standard,count(ln_no) as cnttrl

from Trials_Final

group by type,past_standard

having count(ln_no);

quit;

The above code produces sample output here

Type Past_Standard cnttrl

Trial Mod N 380

Trial Mod Y 12

I brought the results into a proc report

proc report data= trials_final_cnt headskip split='*' wrap nowd;

COLUMNS type Past_Standard ("Trials7" cnttrl) ;

DEFINE Past_Standard /DISPLAY style(column)={JUST=L cellwidth= 100pt tagattr="format:@"}"Past Standard";

DEFINE type /DISPLAY style(column)={JUST=L cellwidth= 100pt tagattr="format:@"}"Type";

DEFINE cnttrl /DISPLAY style(column)={JUST=L cellwidth= 100pt tagattr="format:@"}"Total";

rbreak after /summarize;

COMPUTE after;

type = 'Total';

endcomp;

RUN;

The results pull in everything except the bottom line total. The resuts look exactly like the above dataset. I need to total the field cnttrl In this case add 380+12 so I need to see the total

Re: Grand total in proc report not working

mohamed_zaki — Tue, 16 Sep 2014 15:16:44 GMT

Use ANALYSIS SUM as following:

proc report data= trials_final_cnt headskip split='*' wrap nowd;

COLUMNS type Past_Standard cnttrl ;

DEFINE Past_Standard /DISPLAY style(column)={JUST=L cellwidth= 100pt tagattr="format:@"}"Past Standard";

DEFINE type /DISPLAY style(column)={JUST=L cellwidth= 100pt tagattr="format:@"}"Type";

DEFINE cnttrl / DISPLAY ANALYSIS SUM style(column)={JUST=L cellwidth= 100pt tagattr="format:@"}"Total";

RBREAK AFTER /SUMMARIZE;

COMPUTE after;

type = 'Total';

endcomp;

RUN;

Also please find (Using PROC REPORT To Produce Tables With Cumulative Totals and Row Differences) as a good reference:

Re: Grand total in proc report not working

Cynthia_sas — Tue, 16 Sep 2014 16:16:27 GMT

Hi:

PROC REPORT basic behavior is that when you define a report item as DISPLAY, that means (to PROC REPORT) that you do NOT want totals to be performed. Your numeric variables/columns must have a usage that is associated with a numeric usage -- such as ANALYSIS SUM (or some other statistics). You probably did not need to pre-summarize your data with PROC SQL, because PROC REPORT is capable of producing a summary report. See the attached example. In the code, the statistic N will give a count of observations for every unique combination of Type and Past_Standard. In this case, the RBREAK statement will then summarize a Grand Total at the bottom of the report. Report 2a shows a "detail" report with a Grand Total; Report 2b shows a "group" report with a Grand Total. As you can see, 2a shows all 19 observations, but 2b shows only 2 rows on the final report.

Cynthia

** make some fake data with 19 rows;
data trial_final (keep=type past_standard);

length Type $10 Past_Standard $1;
set sashelp.class;
Type = 'Trial Mod';
if sex = 'F' then Past_Standard = 'Y';
else Past_Standard='N';
run;

title;
footnote;

ods html file='c:\temp\summarize_without_sql.html';
proc print data=trial_final;
title '1) Unsummarized Data with PROC PRINT';
run;

proc report data=trial_final nowd;
title '2a) Proc REPORT example (show every row) with summary';
column type past_standard n;
define type / display;
define past_standard / display;
define n / 'Count';
rbreak after / summarize;
compute after;
type = 'Total';
endcomp;
run;

proc report data=trial_final nowd;
title '2b) Proc REPORT example GROUP report with summary';
column type past_standard n;
define type / group 'Type';
define past_standard / group 'Past Standard';
define n / 'Count';
rbreak after / summarize;
compute after;
type = 'Total';
endcomp;
run;
ods _all_ close;

Re: Grand total in proc report not working

Q1983 — Tue, 16 Sep 2014 19:59:29 GMT

Thanks this helped