BookmarkSubscribeRSS Feed

 

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;

 

 

8 Comments
paulkaefer
Lapis Lazuli | Level 10

This looks like a good example.

PGStats
Opal | Level 21

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
Tourmaline | Level 20
R_is_better
Calcite | Level 5

@PGStats

 

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

PGStats
Opal | Level 21

@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
Calcite | Level 5

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
Quartz | Level 8

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
Tourmaline | Level 20

@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.