Proc means is probably more efficient.
For binary values, the mean is equivalent to the percentage mathematically.
The SQL doesn't work because A, B, C are included in the SELECT statement. The log should have a message indicating that this means it's merging the table with the summary rather than just having the summary data. The SQL is also redundant, the subquery isn't required at all.
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
68
69 proc sql;
70 create table rate as
71 select type, year, A, B, C, sum(A)/count(A) as pctA,
72 sum(B)/count(B) as pctB, sum(C)/count(C) as pctC
73 from (select type, year, A, B, C from have group by type, year)
74 group by type, year;
NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated
table-expression referenced a summary function.
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.RATE created, with 8 rows and 8 columns.
Here are two ways to do this, noting that proc means will scale much easier if you have a lot of variables.
proc means data=have MEAN NWAY;
class type year;
var a b c;
output out=want mean= /autoname;
run;
proc print data=want noobs;
format a_mean b_mean c_mean percent12.1;
run;
proc sql;
create table rate as
select type, year, sum(A)/count(A) as pctA,
sum(B)/count(B) as pctB, sum(C)/count(C) as pctC
from have
group by type, year;
quit;
... View more