Desktop productivity for business analysts and programmers

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

Reply
Contributor
Posts: 44

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

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;

Esteemed Advisor
Posts: 6,661

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

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 9

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

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.

New Contributor
Posts: 2

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

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 ;

Esteemed Advisor
Posts: 5,198

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

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
Occasional Contributor
Posts: 9

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

Thanks. I posted it to SASWare Ballots.

Ask a Question
Discussion stats
  • 5 replies
  • 3263 views
  • 2 likes
  • 5 in conversation