BookmarkSubscribeRSS Feed
_LB
Fluorite | Level 6 _LB
Fluorite | Level 6
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
4 REPLIES 4
Tim_SAS
Barite | Level 11
> 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.
_LB
Fluorite | Level 6 _LB
Fluorite | Level 6
Tim;
Thanks for the first part. That is the first step!
~LB
Cynthia_sas
SAS Super FREQ
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]
_LB
Fluorite | Level 6 _LB
Fluorite | Level 6
Once again-Thanks Cynthia!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 820 views
  • 0 likes
  • 3 in conversation