topic Re: Proc Report Grand Total in ODS and Base Reporting

Proc Report Grand Total

75063 — Wed, 12 Sep 2018 03:18:01 GMT

Hi All,

I have a sas report that has two sub totals named as 'TOTAL' (file attached). I would like to calculate the ''Grand Total' which I have colored in yellow.

I have tried using the following code to calculate the GRAND TOTAL but as expected it adds the sub totals as well and shows the result 76 in place of 38.

more code .......

rbreak after / SUMMARIZE style={color=yellow} ;
compute after;
Academics = 'GRAND TOTAL';
endcomp;
run;

I have not created the "TOTAL" in the Proc report. Rather I have created them in Proc Sql and then used the UNION function to add it to the table before I created the Proc report.

Hence I am unsure of how to get the GRAND TOTAL which should select only the observations namely (leaving , new, old, continue, previous, Interna.) to calculate the 'GRAND TOTAL'.

Please suggest the correct procedure.

Thank you!

Re: Proc Report Grand Total

Reeza — Wed, 12 Sep 2018 03:21:25 GMT

I'd say the mismatch of approaches is the issue. Any reason not to do the same (add total via SQL) for the grand total and add it to the table?

PROC TABULATE is a good option for automatic totals if needed.

In general though, use either PROC REPORT/PRINT/TABULATE throughout or PROC MEANS/SQL to generate the totals

PROC MEANS calculates summary statistics at multiple levels easily which may help here. Look at the WAYS and TYPES statements to see the different options.

Re: Proc Report Grand Total

75063 — Wed, 12 Sep 2018 03:32:44 GMT

Thank you for your suggestion.

Can you please provide a example of how to add selective observations using proc means as is in my case?

Re: Proc Report Grand Total

Reeza — Wed, 12 Sep 2018 04:12:56 GMT

If you provide sample data. What does your data look like to start with?

@75063 wrote:

Thank you for your suggestion.

Can you please provide a example of how to add selective observations using proc means as is in my case?

Re: Proc Report Grand Total

Cynthia_sas — Wed, 12 Sep 2018 19:17:10 GMT

Hi: You do not need to pre-calculate the subtotal. For example, consider this report produced from 2 regions in SASHELP.SHOES:

The BREAK statement produced the subtotal for each region and the RBREAK statement computed the GRAND TOTAL. The challenge if you pre-calculate the subtotals and put them in the data, is that then you have to turn around and exclude them from the grand total unless you pre-calculate the grand total too. It makes more sense to let PROC REPORT (or TABULATE) do it for you.

Cynthia

Re: Proc Report Grand Total

75063 — Sat, 15 Sep 2018 14:39:21 GMT

Thank you Cynthia for your explanation and advice. I think proc Report is a better and smoother way to carry out the operation in this case.

Regards,

Re: Proc Report Grand Total

Reeza — Sat, 15 Sep 2018 15:32:09 GMT

@75063 please mark Cynthia answer as correct, not your own.

Re: Proc Report Grand Total

gordon13267 — Mon, 26 Apr 2021 20:38:53 GMT

I am not sure why the labels for "subtotal" and "grand total" cannot be shown in my excel output. Any idea what's going on?

Thanks,

Re: Proc Report Grand Total

Cynthia_sas — Tue, 27 Apr 2021 18:17:44 GMT

Hi:
What code did you try? What does your COMPUTE block look like? Are your values for FTIC, Dual Credit and Transfer numeric or character variables? Does your code generate LOG messages? If so, what are the LOG messages?

In my example, dated 09/12/2018, you can see my COMPUTE blocks and resulting output. The only reasons I can think of for your labels not to work are some error in your code, some problem with understanding the order of how PROC REPORT processes the report row or some issue with the type (numeric or character) -- but without data or code, it would be just a guess.
Cynthia

Re: Proc Report Grand Total

gordon13267 — Tue, 27 Apr 2021 18:29:46 GMT

Hi,

Yes, you are correct. After correcting the report order, there is no problem at all. Thanks again,