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 */
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.