Add GROUP_CONCAT function to proc sql

[Mike_B]

In MySQL, you can use the group_concat function to concatenate across rows with a by group. I think this would be a valuable addition to the functions available in proc sql.

proc sql; 
create table EXAMPLE as 
select ID, STRING, group_concat(STRING) as ALL_STRINGS 
from DATA_SET 
group by ID; 
quit;

[paulkaefer]

This looks like a good example.

[PGStats]

I would suggest to duplicate the CATS, CATX, CATQ and CATT functions as SQL aggregate operations when there is only one argument to concatenate, similar to other SAS summary functions such as SUM and RANGE which exist both in the data step and SAS/SQL.

For example:

proc sql;
select catx(": ", sex, catx(', ', name)) as nameList
from sashelp.class
group by sex;
quit;

would give

nameList

F: Judy, Jane, Joyce, Barbara, Carol, Mary, Louise, Alice, Janet

M: Philip, James, Henry, John, William, Alfred, Jeffrey, Thomas, Ronald, Robert

[ChrisNZ]

Duplicate of  https://communities.sas.com/t5/SASware-Ballot-Ideas/create-string-summary-functions/idi-p/288035 ?

[R_is_better]

@PGStats

Hi, this doesn't work. Why would you post code along with results that you just made up?

[PGStats]

@R_is_better, maybe, to be more explicit, I should have framed the code with:

/* This code is an example of a proposed SAS/SQL addition. IT DOESN'T WORK IN SAS 9.4 */

[Machtien]

SQL server recently (2017) introduced STRING_AGG and STRING_SPLIT to make the set theory complete.

In many responses to requests for such functions SAS mentioned business value. This is what SAS should not do if many users ask for it.

However to indicate business value for my company (already implemented in SQL server) is:

- make parent table searchable by child table values

- create breadcrumb from grouped list of hits

- create route indication for transport trajectory

- group parent values by a profile created from child values

etc.

So the function is very usefull, and so far very missing in SAS PROC SQL;

[Peter_L]

We use the equivalent function in SAP SQL Anywhere. It has some ordering options, which are useful even essential, otherwise the order of the concatenated strings is arbitrary. The use of an order-by function allows the creation of two lists with the items matching. This is essential for us. Example: list the ingredients of a recipe separated by '+' in alphabetical order and in a separate list list the amounts of the ingredients separated by '+' in the same order. This functionality would make it possible alternatively to order both lists by descending order of ingredient amount.

Syntax:

LIST( [ ALL | DISTINCT ] string-expression [, delimiter-string ] [ ORDER BY order-by-expression [ ASC | DESC ] , ... ] )

Parameters

string-expression
A string expression, usually a column name. When ALL is specified (the default), for each row in the group, the value of string-expression is added to the result string, with values separated by delimiter-string. When DISTINCT is specified, only unique string-expression values are added.

delimiter-string
A delimiter string for the list items. The default setting is a comma. There is no delimiter if a value of NULL or an empty string is supplied. The delimiter-string must be a constant.

order-by-expression
Order the items returned by the function. There is no comma preceding this argument, which makes it easy to use in the case where no delimiter-string is supplied.

[ChrisNZ]

@Peter_L  I see you wandered where good ideas are resting forever undisturbed. 
Please be careful around the SASWare ballot pages, the ghost of the ballot's former usefulness might still be present.