Listagg function help

[Kalai2008]

I am using Listagg function in SAS, Proc sql;(Connected to oracle)- I connect to Oracle and this function works.

I need help in adding comma's for 2 columns. (either Listagg or By process will work)

My current code picks only works for col1.

LISTAGG(col1, ',') WITHIN GROUP (ORDER BY id) AS flag1

Given data

ID         col1           colb

1          blue            US   

1          green          US

1          pink             US

2          blue             US

2          pink             BR

2          green           CA

Want data: (I need Flag1 or Flag2 or both . Flag2 is better for me)

ID   Flag1                                                                 Flag 2

1    ('blue','US', 'green','US','pink','US')                 ('blue-US','green-US','pink-US')

2    ('blue','US','pink','BR','green','CA')                  ('blue-US','pink-BR','green-CA')

My current data: (Listagg function)

ID  flag1

1   ('blue','green',pink')

I can also try the samething using By statement process, but only works for one column.

I need Flag1 or Flag2 or both

Proc sort data= have;
By custId;
Run;

Data want;

Set have;
By ID;
Length list $500.;Retain List;

If first.ID then list= variable;
Else list = catx(', ', list, variable);

If last.ID then output;
Run;

[mkeintz]

---

@Kalai2008 wrote:

I am using Listagg function in SAS, Proc sql;(Connected to oracle)- I connect to Oracle and this function works.

... stuff deleted... (bold italics below are mine):

I can also try the same thing using By statement process, but only works for one column.

I need Flag1 or Flag2 or both

Proc sort data= have;
By custId;
Run;

Data want;

Set have;
By ID;
Length list $500.;Retain List;

If first.ID then list= variable;
Else list = catx(', ', list, variable);

If last.ID then output;
Run;

---

Why do you think it "works only for one column"?  Couldn't you do something like this?

data want;
   set have;
   by id;
   length list1  list2 $200;
   retain list1 list2 ;
   if first.id then call missing(list1,list2);
   list1=catx(',',list1,var1);
   list2=catx(',',list2,var2);
   if last.id then output;
run;

[Kalai2008]

Thanks for checking, Yes, I can still do for 2 columns separately. Please check MY WANT data, I wanted to combine col1 and col2, get results in one column. My data is huge, around 20 million records, so better to use listagg function.

[mkeintz]

I presume you think the listagg function is "better" because it is an in-database function.

But if you need to do it in the sas data step:

data have;
  input ID col1 :$5. colb :$2.;
datalines;
1 blue   US   
1 green  US
1 pink   US
2 blue   US
2 pink   BR
2 green  CA
run;
data want;
  set have;
  by id;
  length flag2 $100;
  retain flag2;
  if first.id then call missing(flag2);
  flag2=catx(',',flag2,quote(catx('-',col1,colb),"'"));
  if last.id;
  flag2=cats('(',flag2,')');
  put  flag2=;
run;

This just nests a catx function inside a quote function inside a catx to produce flag2.

I presume that Oracle allows you to do similar nesting of quoting and within-row concatenating inside the listagg function.

[Kalai2008]

Thank you and it worked.

[s_lassen · Accepted solution]

You can do it by using the || (concatenation) operator in Oracle SQL - just a minimal change of your initial code. e.g.:

LISTAGG(col1||'-'||colb, ',') WITHIN GROUP (ORDER BY id) AS flag2