代码之家 › 专栏 › 技术社区 › SASPYTHON

sas proc sql-如何在sas中执行listagg函数

proc-sql sas

0

SASPYTHON · 技术社区 · 7 年前

下午好

我正在使用proc sql在SAS上查找listagg函数。

例如

id         product_name
1001        Bananas
1002        Bananas
1002        Apples
1002        Peach
1003        Pears

proc sql;
create table work.test2 as
select id, _____(',', product_name)
from  test1
group by id
order by 1;
quit;

结果

    id          product_name

    1001        Bananas
    1002        Bananas,Apples,Peach
    1003        Pears

SAS有这样的功能吗?

2 回复 | 直到 7 年前

1

Kiran 7 年前

你可以做到

data have;
input id product_name $;
datalines;
1001        Bananas
1002        Bananas
1002        Apples
1002        Peach
1003        Pears
 ;


  data want(rename=(product=product_name));
 do until(last.id);
 set have;
 by id;
 length product $50.;
  product =catx(',',product_name, product);
 end;
drop product_name;
run;

2

0

Reeza 7 年前

下面是两种解决类似问题的方法的示例:

是在一个数据步骤中进行的,并在进行过程中积累

将数据转换为宽格式,然后使用cat函数。

*create sample data for demonstration;
data have;
    infile cards dlm='09'x;
    input OrgID Product $   States $;
    cards;
1   football    DC
1   football    VA
1   football    MD
2   football    CA
3   football    NV
3   football    CA
;
run;

*Sort - required for both options;
proc sort data=have;
    by orgID;
run;

**********************************************************************;
*Use RETAIN and BY group processing to combine the information;
**********************************************************************;
data want_option1;
    set have;
    by orgID;
    length combined $100.;
    retain combined;

    if first.orgID then
        combined=states;
    else
        combined=catx(', ', combined, states);

    if last.orgID then
        output;
run;

**********************************************************************;
*Transpose it to a wide format and then combine into a single field;
**********************************************************************;
proc transpose data=have out=wide prefix=state_;
    by orgID;
    var states;
run;

data want_option2;
    set wide;
    length combined $100.;
    combined=catx(', ', of state_:);
run;