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.
I create a function sql_query_concat , maybe you can use like mysql group_concat with something different
select x , y group_concat( z )
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 ;
/*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 ;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.