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;
This looks like a good example.
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.
proc sql;select catx(": ", sex, catx(', ', name)) as nameListfrom sashelp.classgroup by sex;quit;
F: Judy, Jane, Joyce, Barbara, Carol, Mary, Louise, Alice, Janet
M: Philip, James, Henry, John, William, Alfred, Jeffrey, Thomas, Ronald, Robert
Duplicate of https://communities.sas.com/t5/SASware-Ballot-Ideas/create-string-summary-functions/idi-p/288035 ?
Hi, this doesn't work. Why would you post code along with results that you just made up?
@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 */
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
So the function is very usefull, and so far very missing in SAS PROC SQL;
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.
LIST( [ ALL | DISTINCT ] string-expression [, delimiter-string ] [ ORDER BY order-by-expression [ ASC | DESC ] , ... ] )
string-expressionA 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-stringA 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-expressionOrder 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.
@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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.