topic Proc Report Summarize and Mean values in the same row. in ODS and Base Reporting

Proc Report Summarize and Mean values in the same row.

afiehrer — Mon, 30 Nov 2015 18:22:59 GMT

Hi,

I have a question regarding the summarize function in proc report.

I have a table with 3 columns containing numbers and 2 columns containing percentages.

I am wondering if there is any way to get a single row at the bottom of this table with the sum of the number columns and the combined rate of the percentage columns.

I can use BREAK AFTER / SUMMARIZE to get the sums of the number columns, but have not found a way to include the combined rate of the percentages. Is this possible?

Here is my code:

PROC REPORT DATA=SAMPLE_DATA ;
   COLUMNS COMPANY STATE YEAR LOB NUMBER1 PERCENT1 NUMBER2 PERCENT2 NUMBER3 NUMBER4;
   DEFINE COMPANY / ORDER NOPRINT;
   DEFINE STATE / ORDER NOPRINT;
   DEFINE YEAR / 'Year' ORDER format=YEAR4.;
   DEFINE NUMBER1 / FORMAT=DOLLAR15.;
   DEFINE NUMBER2 / FORMAT=DOLLAR15.;
   DEFINE NUMBER3 / FORMAT=DOLLAR15.;
   DEFINE NUMBER4 / FORMAT=DOLLAR15.;
   DEFINE PERCENT1 / FORMAT=PERCENT10.1;
   DEFINE PERCENT2 / FORMAT=PERCENT10.1;
COMPUTE BEFORE _PAGE_ / LEFT;
   LINE COMPANY $CMPYFMT.;
   LINE STATE $STFMT.;
   LINE ' ';
ENDCOMP;
   BREAK AFTER STATE / PAGE SKIP;
   BREAK AFTER YEAR / SUMMARIZE style={font_weight=bold
                cellheight=.25in vjust=b};
RUN;

So I am looking for a summary of each year, essentially the sum of the numbers and combined rate of percents from all LOB in each year, state, company.

Re: Proc Report Summarize and Mean values in the same row.

RW9 — Mon, 30 Nov 2015 14:58:50 GMT

Personally, I always do all my calculations in the datasteps before the report procedure. You can use nice puctions like proc means, summary, freq etc. to do these means, and keep the report code simple.

Re: Proc Report Summarize and Mean values in the same row.

ballardw — Mon, 30 Nov 2015 16:20:43 GMT

Unless your denominators are the exact same size for all your data, the mean of a percentage (or any type of rate defined with a division) is likely to be a very misleading data element to look at.

Example:

Salesman A contacts one customer in a month and makes one sale, so his sales rate is 100%.

Salesman B contacts 2000 customers in a month and makes 1000 sales of the same amount for a sales rate of 50%.

What would an average sales rate of 75% ( 100+50)/2 actually tell you?

A combine rate of (1 + 1000) / (1 +2000), which is what proc report will generally do probably tells you a lot more about what is going on with your sales staff.

Re: Proc Report Summarize and Mean values in the same row.

afiehrer — Mon, 30 Nov 2015 17:47:22 GMT

This is what I figured I might need to do, I just wanted to see if there was any way to get there in the report. Thanks for the response!

Re: Proc Report Summarize and Mean values in the same row.

afiehrer — Mon, 30 Nov 2015 17:53:31 GMT

You are correct. I want a combined rate in the summary row of the table instead of a mean. That was my mistake.

Is there any way to get this combined rate of the percentages to appear in the same row as the sums of my number columns (ie a "total" row)?

Re: Proc Report Summarize and Mean values in the same row.

ballardw — Mon, 30 Nov 2015 18:11:44 GMT

You should post the Proc Report code you are using. Without knowing how you are creating your current values it might be a tad difficult to guess how to calculate what you need.