Assume that the values of X in DETAIL are counts, and you
would like to know what percentage of the total count they
represent. PROC SQL provides a neat solution:
proc sql;
create table combined as
select id, x,(x/sum(x))*100 as percent
from detail;
quit;
PROC SQL is very flexible when it comes to summarizing data. In
this instance, it can interpret your intentions very simply. If you
request only a summary variable, for example, sum(x), it would
return only a single record, that of the sum. However, since you
are selecting detailed records as well, two passes through the
data are made: one to calculate the summary statistic and the
other to remerge it back to the individual rows. Note also that you
do not have to explicitly KEEP or DROP the sum of X - you use
it only for the purpose of calculating PERCENT. If you run this
code, you will see the following note in the log:
NOTE: The query requires remerging summary
statistics back with the original data.
This remerging is actually a SAS enhancement that is not
typically available in standard SQL. The more general approach
would be to first calculate the sum and then combine it with all the
rows in a separate step as follows:
proc sql;
create view sum_view as
select sum(x) as sum_x
from combined;
create table combined as
select id, x, (x/sum_x)*100 as percent
from detail,sum_view;
quit;
In the above example, the sum of X is calculated using a view
called SUM_VIEW. In the second SELECT statement, PERCENT
is calculated using SUM_X from the view SUM. This approach is
also more general, in that the summary measure does not have
to be derived from the same detailed data.
