topic PROC REPORT-SUMMARIZING DATA in ODS and Base Reporting

PROC REPORT-SUMMARIZING DATA

_LB — Tue, 18 Jan 2011 20:55:14 GMT

Hello all;
I have a couple of questions regarding Proc report/Proc SQL in terms of summarizing data..

What I have is this...
month rater compliance
NOV10 A 1
NOV10 A 0
NOV10 A 1
NOV10 A 1
NOV10 B 0
NOV10 B 1
DEC10 A 1
DEC10 A 0
DEC10 A 0
DEC10 A 0
DEC10 B 1
DEC10 B 1

What I can get using proc sql code:

proc sql;
create table HH2 as select distinct month, rater length 30, sum(compliance) as compliance, count(*) as counts,
sum(compliance2)/count(*) format percent10. as compliance_rate
from HH
group by rater;
order by month;
quit;

I get:
Month RATER COUNT Compliance (sum) Compliance rate
NOV10 A 4 3 75%
NOV10 B 2 1 50%
DEC10 A 4 1 25%
DEC10 B 2 2 100%

what I need to get is:

Month RATER COUNT Compliance (sum) Compliance rate
NOV10 A 4 3 75%
NOV10 B 2 1 50%
TOTAL 6 4 67%

DEC10 A 4 1 25%
DEC10 B 2 2 100%
TOTAL 6 3 50%

I have started with proc report but not have had much success. One of the issues is that when I group the months, its puts them in alpha order-Dec10 Nov10 rather than month/year order and I am not sure why. The dates are legitimate dates.

I would be willing to do this is in proc report or proc sql but proc sql, whatever is easiest!

Thanks so much for your help again!
~Lawrence

Re: PROC REPORT-SUMMARIZING DATA

Tim_SAS — Tue, 18 Jan 2011 21:03:55 GMT

> One of the issues is that when I group the
> months, its puts them in alpha order-Dec10 Nov10
> rather than month/year order and I am not sure why.
> The dates are legitimate dates.

By default, PROC REPORT orders the values of ORDER and GROUP variables by the formatted value. To order by the unformatted value, add the ORDER=INTERNAL option to the DEFINE statement for that column.

(Don't confuse the ORDER option with the ORDER= option. The ORDER option says to order this column. The ORDER= option says how to order it.)

See ORDER=DATA|FORMATTED|FREQ|INTERNAL in the PROC REPORT documentation for the DEFINE statement.

Re: PROC REPORT-SUMMARIZING DATA

_LB — Tue, 18 Jan 2011 21:11:16 GMT

Tim;
Thanks for the first part. That is the first step!
~LB

Re: PROC REPORT-SUMMARIZING DATA

Cynthia_sas — Wed, 19 Jan 2011 00:31:04 GMT

Hi:
I generally use PROC TABULATE if I need more complicated percents (such as percent of row, percent of column, etc), but look at this report for PROC REPORT using SASHELP.CLASS, which has 19 total students (9 females and 10 males). For this report, I show the count and the percent of the grand total for each age group and each sex, so you can see how the BREAK and RBREAK statements work with the data and with the groups.

cynthia
[pre]
ods html file='c:\temp\output\percent.html' style=sasweb;

proc report data=sashelp.class nowd;
column sex age n pctn;
define sex / group;
define age / group;
define n / 'Count';
define pctn / 'Percent' f=percent9.2;
break after sex / summarize;
rbreak after / summarize;
run;
ods html close;
[/pre]

Re: PROC REPORT-SUMMARIZING DATA

_LB — Wed, 19 Jan 2011 18:53:31 GMT

Once again-Thanks Cynthia!