How to use GROUP BY to concatenate strings in SAS proc SQL?

[sebster24]

Basically the question is how to get from this:

ID	String
1	A
1	B
2	C

to this:

ID	String
1	A, B
2	C

this is my code:

proc sql;

SELECT ID, GROUP_CONCAT((string separated by ' ')

FROM work.tmp

GROUP BY ID

;

run;

[Kurt_Bremser]

GROUP_CONCAT is a function specific to MySQL and not (AFAIK) part of the SQL standard used by SAS to build PROC SQL.

In SAS you do such things with a data step:

data want (drop=string_old);

set tmp (rename=(string=string_old));

by id;

retain string;

length string $ 20; * set large enough to accommodate the maximum number of records per ID;

if first.id then string = '';

string = catx(',',trim(string),string_old);

if last.id then output;

run;

[Mike_B]

It's been a year and a half since the original post, so I'm checking in to see if there still is not a way to concatenate by group in proc sql. Like the original poster, I tried group_concat to no avail. Thanks.

[andywang]

Hi ,

I create a function sql_query_concat , maybe you can use like  mysql group_concat with something different 

select x , y group_concat( z ) 

from test_monotonic

group by x ,y 

;

/* demo data create */
data test_monotonic;
length x $3. ;
length y $1. ;
x = "a" ; y = "b" ; z = 3 ; output ;
x = "a" ; y = "b" ; z = 1 ; output ;
x = "aa" ; y = "b" ; z = 3 ; output ;
x = "aa" ; y = "b" ; z = 2 ; output ;
x = "aaa" ; y = "b" ; z = 3 ; output ;
x = "aaa" ; y = "b" ; z = 3 ; output ;
run ;

/* pass sql command (sql_cmd ) as parameter and concat the result with optional delimiter parameter (DLM) */
%macro sql_query_concat( sql_cmd ,DLM=%str(,), OUT=qry_result ) ;
%local qry_table ;
%global &OUT. ;
%let sql_cmd = %qsysfunc( dequote( &sql_cmd. ) ) ;
%let qry_table = sqlQuery%substr(%sysfunc(compress( %sysfunc(time())*%sysfunc(ranuni(32767))*100)) ,1,5 ) ;
proc sql noprint;
create table &qry_table. as
%unquote( &sql_cmd. )
;
select *
into : &OUT. separated by "&DLM."
from &qry_table.
;
drop table &qry_table.
;
quit ;
%mend ;
/* for run_macro usage */
%macro Fsql_query_concat() ;
%let sql_cmd = %qsysfunc(dequote( &sql_cmd. ) ) ;
%let DLM = %qsysfunc( dequote( &DLM. ) ) ;
%sql_query_concat( &sql_cmd. ,DLM=&DLM., OUT=OUTF )
%mend ;
/* create function version of macro sql_query_concat */
proc fcmp outlib=work.func.tmp;
function sql_query_concat( sql_cmd $ , DLM $ ) $ ;
LENGTH OUTF $256 ;
rc = run_macro( 'Fsql_query_concat' , sql_cmd ,DLM ,OUTF ) ;
return(OUTF);
endsub ;
quit ;
options cmplib=work.func ;

/*by determined the column x and y , concat the records into a string */
proc sql ;
select distinct x , y ,sql_query_concat("select z from test_monotonic where x = '"|| test_monotonic.x || "' and y = '" || test_monotonic.y || "'", ",")
from test_monotonic
;
quit ;

[lsilochan]

This actually worked for me without the rename function and replacing tmp with my original data set. Keep in mind, the original data set has to be sorted by id.
proc sort data=original_data; by id; run;
data want (drop=string_old); set original_data; by id;
retain string;
length string $200;
if first.id then string = '';
string = catx(',',trim(string),string_old);
if last.id then output;
run;

[carlosgg]

This worked for me, too. Thank you for sharing it! 👍👍👍

[PGStats · Accepted solution]

A useful coding tool for doing this is the DO UNTIL() loop

proc sort data=sashelp.class out=class; 
by sex age; 
run;

data want (keep=sex age names);
length names $200;
do until(last.age);
    set class; by sex age;
    names = catx(", ", names, name);
    end;
run;

proc print data=want; 
var sex age names; 
run;

Note that the CATX function trims its arguments, so there is no need to call TRIM.

You can join a long outstanding (since 2017) request for this functionnality in SAS/SQL at

https://communities.sas.com/t5/SASware-Ballot-Ideas/Add-GROUP-CONCAT-function-to-proc-sql/idi-p/3230...

[LinusH]

I'm not sure I see any relationship business use case for this, IMO this "destroys" the data structure.
Nevertheless I'm not aware of any such enhancements since the OP.
If you wish make your wish for product development more visible create a ballot item.

[Mike_B]

Thanks. I posted it to SASWare Ballots.

[carlosgg]

Hello @LinusH, Our application was to put in a single row all the courses a student is taking or took in an academic term.

student_id	academic_term	course
123	Fall 2022	MAT101
123	Fall 2022	ENG101
123	Fall 2022	CMP101
123	Spring 2023	MAT102
123	Spring 2023	ENG102
123	Spring 2023	POS2041

student_id	academic_term	courses	course_count
123	Fall 2022	MAT101; ENG101; CMP101	3
123	Spring 2023	ENG102; POS2041	2

[ballardw]

---

@carlosgg wrote:

Hello @LinusH, Our application was to put in a single row all the courses a student is taking or took in an academic term.

student_id	academic_term	course
123	Fall 2022	MAT101
123	Fall 2022	ENG101
123	Fall 2022	CMP101
123	Spring 2023	MAT102
123	Spring 2023	ENG102
123	Spring 2023	POS2041

student_id	academic_term	courses	course_count
123	Fall 2022	MAT101; ENG101; CMP101	3
123	Spring 2023	ENG102; POS2041	2

---

@PGStats shows the approach to use with sorted data by student and academic term.

Make sure your 'courses" variable is assigned a length long enough to hold the longest expected list of values and don't forget to count the ; and space between courses.

[carlosgg]

👍Thank you @ballardw . The solution by @PGStats looks good. I used @lsilochan 's solution and it worked for me.