SAS PROC SQL - 按变量将变量值连接成单个值 [英] SAS PROC SQL - Concatenate variable values into a single value by group

问题描述

我有一个包含'factor'值和相应'response'值的数据集：

  data inTable; 
输入的事实$ val $; 
 datalines; 
a 1 
a 2 
a 3 
b 4 
b 5 
b 6 
c 7 
d 8 
e 9 
e 10 
f 11 
; 
跑; 
  

我想按因子汇总响应选项，即我需要获得

我非常清楚如何在循环运行的数据步骤中执行此操作值并应用CATX（已发布这里）。但是，我可以使用PROC SQL执行相同的操作吗？使用GROUP BY和SUM（）或CATX（）的某些字符类似的组合？

感谢您的帮助， p>

Dmitry

解决方案

数据步骤是适用于SAS如果你想应用任何类型的逻辑，从前面的行向前传递大量的值。

任何SQL解决方案都是极其不便的 - 您需要将输入表本身连接n次，其中n是任何因素的最大不同值，您还需要定义一个顺序键，以保留用于连接的行顺序。

您可以在proc sql中使用的聚合函数列表可在此处获得：
http://support.sas.com/kb/25/279 .html

虽然其中有些功能可以处理字符变量，但是没有字符串连接的聚合函数。

I have a data set which contains 'factor' values and corresponding 'response' values:

data inTable;
  input fact $ val $;
  datalines;
  a 1
  a 2
  a 3
  b 4
  b 5
  b 6
  c 7
  d 8
  e 9
  e 10
  f 11
;
run;

I want to aggregate response options by factor, i.e. I need to get

I know perfectly well how to implement this in a data step running a loop through values and applying CATX (posted here). But can I do the same with PROC SQL, using a combination of GROUP BY and some character analog of SUM() or CATX()?

Thanks for help,

Dmitry

解决方案

The data step is the appropriate tool to use in SAS if you want to apply any sort of logic that carries lots of values forward from previous rows.

Any SQL solution would be extremely unwieldy - you would need to join the input table to itself n times, where n is the maximum number of distinct values for any of your factors, and you would also need to define a sequential key preserving the row order to use for the join.

A list of aggregation functions you can use in proc sql is available here: http://support.sas.com/kb/25/279.html

Although a few of these do work with character variables, there is no aggregation function for string concatenation.

这篇关于SAS PROC SQL - 按变量将变量值连接成单个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！