MySQL下group_concat函数的SAS实现
MySQL下的字符串聚合函数group_concat是一个非常实用的字符串合并函数，
sas下的宏实现，
%macro group_concat(input, output, id, string, length);
data &output (drop=string_old);
set &input (rename=(&string=string_old));
by &id;
retain &string;
length &string $ &length; * set large enough to accommodate the maximum number of records per ID;
if first.&id then &string = '';
brand = catx(',',trim(&string),string_old);
if last.&id then output;
run;
%mend group_concat;
基本表结构如下,
| 按字母排序的变量和属性列表 | ||||||
|---|---|---|---|---|---|---|
| # | 变量 | 类型 | 长度 | 输出格式 | 输入格式 | 标签 |
| 2 | brand | 字符 | 40 | $40. | $40. | brand |
| 1 | customer_id | 字符 | 40 | $40. | $40. | customer_id |
宏调用方法，
%group_concat(Last_brands,want,customer_id,brand,300);
对应的MySQL的group_concat是，
create table want as select customer_id, group_concat(brand) as brand from last_brands group by 1;
P.S.
PostgreSQL下的替代实现是string_agg和array_agg.
references,
