BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
sebster24
Quartz | Level 8

Basically the question is how to get from this:

IDString
1A
1B
2C

to this:

IDString
1A, B
2C

this is my code:

proc sql;

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

FROM work.tmp

GROUP BY ID

;

run;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

 

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.

 

PGStats_0-1698528599851.png

 

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

 

 

PG

View solution in original post

11 REPLIES 11
Kurt_Bremser
Super User

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
Obsidian | Level 7

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
Calcite | Level 5

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
Calcite | Level 5
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
Fluorite | Level 6
This worked for me, too. Thank you for sharing it! 👍👍👍
PGStats
Opal | Level 21

 

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.

 

PGStats_0-1698528599851.png

 

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

 

 

PG
LinusH
Tourmaline | Level 20
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.
Data never sleeps
Mike_B
Obsidian | Level 7

Thanks. I posted it to SASWare Ballots.

carlosgg
Fluorite | Level 6

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_termcourse
123Fall 2022MAT101
123Fall 2022ENG101
123Fall 2022CMP101
123Spring 2023MAT102
123Spring 2023ENG102
123Spring 2023POS2041

 

student_idacademic_termcoursescourse_count
123Fall 2022MAT101; ENG101; CMP1013
123Spring 2023ENG102; POS20412
ballardw
Super User

@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
Fluorite | Level 6

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 38722 views
  • 14 likes
  • 9 in conversation