SAS Proc SQL 中的列表聚合和组连接 [英] List Aggregation and Group Concatenation in SAS Proc SQL

问题描述

我有一个数据集，必须根据粒度(FIELD1 & FIELD2)进行汇总.必须总结两个指标字段(METRIC1 和 METRIC2).到目前为止，这似乎是一项简单的 GROUP BY 任务.但是我有一个字符串字段(FLAG)，它也必须通过连接不同的值来汇总.

I have a dataset which has to be rolled up based on the granularity(FIELD1 & FIELD2). Two of the metrics fields(METRIC1 & METRIC2) have to be summed up. Until now it seems to be an easy GROUP BY task. But I have a string field(FLAG) which has to be rolled up too, by concatenating the distinct values.

可以使用 LISTAGG() 函数在 Oracle 中执行此操作.请帮助我在 SAS Proc SQL 中实现同样的目标.

This operation can be performed in Oracle using the LISTAGG() function. Kindly help me out in achieving the same in SAS Proc SQL.

推荐答案

我不相信在 SAS 中有直接的方法可以做到这一点.CATS(和类似的连接函数)不是聚合函数.几年前有人建议将这些添加回来，但我所知道的没有任何结果(请参阅 这个线程.)

I don't believe there's a direct way to do this in SAS. CATS (and similar concatenation functions) aren't aggregation functions. It was suggested to add these back a few years ago but nothing came of it that I'm aware of (see this thread.)

如果我理解正确，您所做的是 GROUP BY field1/field2，SUM metric1/metric2，并创建一个连接所有看到的 FLAG 字段值的单个 FLAG 字段(但不按它们分组).

If I understand right, what you're doing is GROUP BY field1/field2, SUM metric1/metric2, and make a single FLAG field that concatenates all seen FLAG field values (but doesn't group by them).

我处理这个问题的方法是首先进行聚合 (field1/field2)，然后将其加入到一个单独的表中，该表只是 field1/field2/flag.您可以在数据步骤中最轻松地做到这一点，例如:

The way I would handle this is to first do your aggregation (field1/field2), and then join that to a separate table that was just field1/field2/flag. You could make that most easily in the data step, something like:

data want;
  set have;
  by field1 field2;
  length flag_out $100; *or longer if you need longer;
  flag_out = catx(',',flag_out,flag);
  if last.field2 then output;
  rename flag_out=flag;
  drop flag;
run;

这假设它已经按 field1/field2 排序，否则你需要先这样做.

This assumes it's sorted already by field1/field2, otherwise you need to do that first.

这篇关于SAS Proc SQL 中的列表聚合和组连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！