处理向表中添加汇总行的更简洁方法? [英] Cleaner way of handling addition of summarizing rows to table?

问题描述

我有一个由 5 个变量组成的数据集，其中包含两个因变量.我的目标是让这个数据集附加额外的行，其中 TOTAL 作为自变量的值，因变量的值相应地发生变化.

I have a dataset that is unique by 5 variables, with two dependent variables. My goal is for this dataset to have appended to it additional rows with TOTAL as the value of independent variables, with the values of the dependent variables changing accordingly.

为单个自变量执行此操作不是问题，我会按照以下方式做一些事情:

To do this for a single independent variable is not a problem, I would do something along the lines of:

proc sql;
create table want as
select "TOTAL" as independent_var1,
independent_var2,
...
independent_var5,
sum(dependent_1) as dependent_1,
sum(dependent_2) as dependent_2
from have
group by independent_var1,...,independent_var5;
quit;

然后以您选择的任何方式附加原始数据集.但是，我想要上面的 x5 (对于每个自变量)，然后再次对于 5 个自变量中的 TOTAL/nontotal 的每个可能组合.不知道有多少数据集在我脑海中浮现……但数量相当可观.

Followed by appending the original dataset in whatever fashion you choose. However, I want the above, yet x5 (for each independent variable), and then again for each possible combination of TOTAL/nontotal across the 5 independent variables. Not sure just how many datasets that is off the top of my head...but it's a decent amount.

所以到目前为止我想出的最佳策略是使用上面的一些温和的创意宏代码来生成所有可能的总计/非总计表组合，但似乎 SAS 可能有更好的方法，也许隐藏在我从未听说过的深奥的 proc 步骤中......

So best strategy I've come up with so far is to use the above with some mildly creative macro code to generate all possible table combinations of total/non-total, but it seems like SAS just might have a better way, maybe tucked away in an esoteric proc step I've never heard of...

--

尝试展示示例，使用三个自变量和一个因变量:

Attempt to show example, using three independent variables and 1 dependent variable:

Ind1|2|3|Dependent1
0 0 0 1
0 0 1 3
0 1 0 5
0 1 1 7

期望的输出是:

0 0 ALL 4
0 1 ALL 12
0 ALL 0 6
0 ALL 1 10
ALL 0 0 1
ALL 0 1 3
ALL 1 0 5
ALL 1 1 7
0 ALL ALL 16
ALL 0 ALL 4
ALL 1 ALL 12
ALL ALL 0 6
ALL ALL 1 10
ALL ALL ALL 16
0 0 0 1
0 0 1 3
0 1 0 5
0 1 1 7

我可能忘记了一些组合，但这应该有助于理解这一点.

I may have forgotten some combinations, but that should serve to get the point across.

推荐答案

PROC MEANS 应该为你做这件事.您需要清理输出以使其与您想要的完全匹配(在您的示例中缺少 INDx = "ALL")，否则它可以正确完成计算.

PROC MEANS should do this for you trivially. You need to clean up the output in order to get it to perfectly match what you want (missing for INDx = "ALL" in your example) but otherwise it gets the calculations done properly.

data have;
input Ind1 Ind2 Ind3 Dependent1;
datalines;
0 0 0 1
0 0 1 3
0 1 0 5
0 1 1 7
;;;;
run;

proc means data=have;
class ind1 ind2 ind3;
var dependent1;
output out=want sum=;
run;

这篇关于处理向表中添加汇总行的更简洁方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！