BookmarkSubscribeRSS Feed
Brian
Obsidian | Level 7

Is there a equivalent function to MySQL's "group_concat" in SAS PROC SQL? I would like concatenate multiple associated rows into a composite key. There is no max/min limit to the number of rows. As such, transposing this field into columns may not be appropriate. Group_concat would allow me to avoid additional data/code manipulation with each change to the query.

Thanks

2 REPLIES 2
art297
Opal | Level 21

See if the response provided in the following post answers your question:

http://www.rhinocerus.net/forum/soft-sys-sas/485907-re-simulating-group_concat-sql-agregate-function...

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 ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 2 replies
  • 5111 views
  • 3 likes
  • 3 in conversation