PROC REPORT

[JibJam221]

hi! Currently, I am trying to use PROC REPORT to product a report that contains a grand totals row at the bottom (sum of each column). First, I created a table containing the data using PROC SQL: 

--------------------------------------------------------------------------------------------

proc sql;
create table tbl_AP as
 select
  Grl,
  count(PI) as Total,
  count (AR) as Inis
from w.empWFA
 where OCP= 3
 group by Grl;
quit;

--------------------------------------------------------------------------------------------

Then, I used PROC REPORT to create the report, specifically using RBBREAK to get the grand totals:

--------------------------------------------------------------------------------------------

proc report data= tbl_AP
   headline headskip;
 Title1 "Distribution of People ";
 column Grl Total Inis;
  define Grl / group 'Gp-LV';
  define Total / display;
  define Inis / display;
  rbreak after /summarize;

run;

--------------------------------------------------------------------------------------------

Everytime, I run this program, I get 0 errors or warnings. The table with the proper titles and labels appear, however there is never a totals row at the bottom. Instead, just an empty row at the bottom. Could anyone provide any insight on where my code is wrong? 

Thanks!

[Cynthia_sas]

HI:
Read about PROC REPORT usage. Essentially, when you put "DISPLAY" as the usage (after the / in the DEFINE statement), you turned off any numbers on the summary line. Change the DISPLAY to SUM for TOTAL and INIS and the RBREAK will show you numbers.

Cynthia

[JibJam221]

hi Cynthia! This worked perfectly, thanks! Now, is there any way for me to label the final row as "grand total"? The Grl column is not numerical, and contains characters. I was hoping to put the grand total label at the end of this column, followed by the computed totals of the other two columns (inis and Total).

thanks!

[Cynthia_sas · Accepted solution]

Hi:
Yes, but you need a COMPUTE block for that. You also have to be sure that your GRL variable is wide enough to hold the string "Grand Total" (11 characters). For example, if GRL is only 2 characters wide, then you would only be able to fit 'Gr' on the summary line by default and would have to use other methods of placing Grand Total. Look at this previous forum posting https://communities.sas.com/t5/SAS-Procedures/Grand-Total/td-p/49481 for an example.

And for more advanced COMPUTE block examples, here's a paper: https://support.sas.com/resources/papers/proceedings17/SAS0431-2017.pdf that might be of interest.

Cynthia