Proc Report Grand Total

[75063]

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! 

Rs

[Reeza]

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.

[75063]

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? 

[Reeza]

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? 

---

[Cynthia_sas · Accepted solution]

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

[75063]

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,

Rs

[Reeza]

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

[gordon13267]

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,

[Cynthia_sas]

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

[gordon13267]

Hi, 

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