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;
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
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;
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.
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 ;
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
Thanks. I posted it to SASWare Ballots.
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 |
@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.
👍Thank you @ballardw . The solution by @PGStats looks good. I used @lsilochan 's solution and it worked for me.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.