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

Grand Total Columns with Proc Report

Elliott — Wed, 03 Oct 2018 20:06:39 GMT

Hello,

I am using proc report for an ods output but I need to have the columns totaled at the bottom. I have been researching and have gotten the line at the end of my data that says Grand Total.. but non of my columns have totals in them. I have tried using compute but have not been successful.

this is what I have so far:

proc report nofs data=bank_rpt list;

title "Retail Bank";

column category calls percent_calls transfers percent_xfers xfer_rate ;

define category / display 'Category';

define calls / analysis sum display 'IVR Calls';

define percent_calls / analysis sum display '% of IVR Calls';

define transfers / analysis sum display 'Xfer to IVR or Agent' ;

define percent_xfers / analysis sum display '% of Transfers';

define xfer_rate / analysis sum display'Transfer Rate';

rbreak after / summarize

style(summary) = {font_weight=bold font_size=3};

compute after ;

category = "Grand Total";

endcomp;

run;

so I don't know what to add to get my columns to sum.

data sample:

category calls transfers percent_calls percent_xfers xfer_rate
Activity 1,821,243 455,242 56.50% 43.79% 25.00%
Unauthenticated 637,303 249,487 19.77% 24.00% 39.15%
Authentication 421,053 187,932 13.06% 18.08% 44.63%
Main Menu 175,487 85,933 5.44% 8.27% 48.97%

TIA

Elliott

Re: Grand Total Columns with Proc Report

SASKiwi — Wed, 03 Oct 2018 21:10:18 GMT

Try changing your definition of CATEGORY to use GROUP rather than DISPLAY:

define category / group 'Category';

Re: Grand Total Columns with Proc Report

Cynthia_sas — Thu, 04 Oct 2018 14:20:06 GMT

Hi:
Is that the actual program you're using to read the data? SAS will not read commas and percents using a simple INPUT statement. well, you don't actually show a valid INPUT statement in your program.

So I am not sure what your data looks like but that program won't create the data.

However, you have another issue and it is this...for all your numeric variables, you have:
DEFINE num_var / analysis sum display ...;

so the last usage you have is DISPLAY for your numeric variables and that is essentially turning off the analysis sum usage that you need for the numeric variables to be summed.

I think if you just take off DISPLAY from all of your numeric variables you'll be OK (assuming that the commas and % were the cosmetics that you typed --I just wonder because I don't see any formats in your DEFINE Statements and the use of commas and percents could indicate that those variables are character variables.

This worked for me:

Cynthia

Re: Grand Total Columns with Proc Report

Scooby3g — Fri, 23 Sep 2022 14:36:35 GMT

Hi Cynthia,
what if you only want the sum of two of the items in the category column?

Re: Grand Total Columns with Proc Report

Cynthia_sas — Fri, 23 Sep 2022 17:01:37 GMT

Hi:

I'm not exactly sure what you're asking. If you want to turn off the summary for 2 of the columns, then you need to change the USAGE to DISPLAY, as shown below:

If you only want to add up 2 or 3 of the categories, then you'd need to do some pre-processing or pre-summarizing, probably make some helper variables. I would make a copy of the dataset like this and turn one category's helper variables into missing:

So then, when I do the PROC REPORT, I can move the helper variables under the correct column in the COMPUTE AFTER block:

The above screen shot shows the progression from creating the helper variables and using them in 3a) to moving the helper variable values on the summary line over to the report columns (3b) to the final report with the helper variables hidden (3c).

Here's the code for 3c:

cynthia

Re: Grand Total Columns with Proc Report

Scooby3g — Fri, 23 Sep 2022 17:50:45 GMT

Thanks so much Cynthia!!