Grand total in proc report not working

[Q1983]

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

[mohamed_zaki · Accepted solution]

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:

[Cynthia_sas]

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;

[Q1983]

Thanks this helped