当某些子组因子为 0 时，按组和子组计算行数 [英] Count rows number by group and subgroup when some subgroup factor is 0

问题描述

我知道如何通过 proc freq 或 sql 计算组和子组数.我的问题是当子组中的某个因子缺失时，我仍然想将缺失因子显示为 0.我该怎么做?例如，数据集是:

I know how to count group and subgroup numbers through proc freq or sql. My question is when some factor in the subgroup is missing, and I still want to show missing factor as 0. How can I do that? For example, the data set is:

group1 group2
1      A
1      A
1      A
1      A
2      A
2      B
2      B

我想要一个结果:

group1 group2 N
1      A      4
1      B      0
2      A      1
2      B      2

如果我只使用默认的 SAS 设置，它通常会显示为

If I only use the default SAS setting, it will usually show as

group1 group2 N
1      A      4
2      A      1
2      B      2

但我仍然希望结果中的第二行告诉我该类别中有 0 个观察值.

But I still want to the second line in the result tell to me that there are 0 observations in that category.

推荐答案

Reeza 的 sparse 选项有效，只要每个组在您的数据中至少出现一次.假设有一个 group1 3 未在您的数据中表示，并且您仍然希望它们显示在频率表中.如果是这种情况，解决方案是创建一个包含所有类别的参考表，然后将您的频率表正确地加入其中.

Reeza's sparse option works as long as each group is represented in your data at least once. Suppose there were a group1 3 that is not represented in your data, and you would still want them to show up in the frequency table. If that is the case, the solution is to create a reference table with all of your categories then right join your frequency table to it.

创建参考表:

data ref;
  do group1 = 1 to 3;
    group2 = 'A';
    output;
    group2 = 'B';
    output;
  end;
run;

用proc sql创建频率表，右连接引用表:

Create the frequency table with proc sql, right joining to the reference table:

proc sql;
select
  r.group1,
  r.group2,
  count(h.group1) as freq
from
  have h
  right join ref r
  on h.group1 = r.group1
  and h.group2 = r.group2
group by
  r.group1,
  r.group2
order by
  r.group1,
  r.group2
;
quit;

这篇关于当某些子组因子为 0 时，按组和子组计算行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！