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;
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
@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;
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.
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: