Help using Base SAS procedures

Grand total in proc report not working

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 131
Accepted Solution

Grand total in proc report not working

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


Accepted Solutions
Solution
‎09-16-2014 11:16 AM
Super Contributor
Posts: 490

Re: Grand total in proc report not working

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:

View solution in original post


All Replies
Solution
‎09-16-2014 11:16 AM
Super Contributor
Posts: 490

Re: Grand total in proc report not working

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:

SAS Super FREQ
Posts: 8,740

Re: Grand total in proc report not working

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;

Frequent Contributor
Posts: 131

Re: Grand total in proc report not working

Thanks this helped

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 1975 views
  • 3 likes
  • 3 in conversation