回複:問個SQL的問題:

回答: 問個SQL的問題:小謀2011-06-28 06:43:36


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.

所有跟帖: 

厲害! -淡淡鬱金香- 給 淡淡鬱金香 發送悄悄話 淡淡鬱金香 的博客首頁 (0 bytes) () 06/28/2011 postreply 07:53:19

虎笑長曰:"遇事古狗,不原創". 樂見淡淡鬱金香,解愁拔牙謀女郎 -美國老土- 給 美國老土 發送悄悄話 美國老土 的博客首頁 (211 bytes) () 06/28/2011 postreply 08:09:14

thanks! -小謀- 給 小謀 發送悄悄話 小謀 的博客首頁 (0 bytes) () 06/28/2011 postreply 08:09:06

laotu -大灰狼太太- 給 大灰狼太太 發送悄悄話 (0 bytes) () 06/29/2011 postreply 05:47:02

請您先登陸,再發跟帖!