topic Re: How to use GROUP BY to concatenate strings in SAS proc SQL? in SAS Enterprise Guide

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

sebster24 — Thu, 18 Jun 2015 09:05:47 GMT

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;

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

Kurt_Bremser — Thu, 18 Jun 2015 11:34:29 GMT

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;

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

Mike_B — Thu, 05 Jan 2017 20:07:11 GMT

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.

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

LinusH — Fri, 06 Jan 2017 14:10:32 GMT

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.

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

Mike_B — Fri, 06 Jan 2017 21:43:46 GMT

Thanks. I posted it to SASWare Ballots.

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

andywang — Fri, 03 Feb 2017 03:01:15 GMT

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 ;

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

lsilochan — Mon, 06 Apr 2020 14:34:00 GMT

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;

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

carlosgg — Thu, 26 Oct 2023 13:20:53 GMT

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

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

PGStats — Sat, 28 Oct 2023 21:45:20 GMT

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/323092

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

carlosgg — Mon, 30 Oct 2023 12:49:01 GMT

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

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

ballardw — Mon, 30 Oct 2023 16:10:09 GMT

@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.

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

carlosgg — Mon, 30 Oct 2023 17:18:34 GMT

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