topic Re: How to CONCAN by Group in SAS in SAS Programming

How to CONCAN by Group in SAS

hellohere — Mon, 24 Jan 2022 23:51:55 GMT

Try to CONCAN string into a line. Oracle has group_concan(). How to do it in SAS/SQL? Anyone

proc sql;
create table summ_peek as
select case when cylinders>=6 then 1 else 0 end as flag, count(*) as cttot, concan(make) as make
from sashelp.cars
group by 1;
quit;

Re: How to CONCAN by Group in SAS

Reeza — Mon, 24 Jan 2022 23:59:04 GMT

Here are two ways:

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

EDIT: That SQL will remerge with original table though, giving you 1 record per observation as the first calculation is row level, not an aggregate calculation.

ORACLE would error out I believe.

@hellohere wrote:

Try to CONCAN string into a line. Oracle has group_concan(). How to do it in SAS/SQL? Anyone

proc sql;
create table summ_peek as
select case when cylinders>=6 then 1 else 0 end as flag, count(*) as cttot, concan(make) as make
from sashelp.cars
group by 1;
quit;

Re: How to CONCAN by Group in SAS

hellohere — Tue, 25 Jan 2022 00:17:09 GMT

Do you mind to Cut-&-Paste the webpage( text only is fine)? I cannot access that webpage. Thanks

Re: How to CONCAN by Group in SAS

Reeza — Tue, 25 Jan 2022 01:15:57 GMT

*create sample data for demonstration;
data have;
    infile cards dlm='09'x;
    input OrgID Product $   States $;
    cards;
1   football    DC
1   football    VA
1   football    MD
2   football    CA
3   football    NV
3   football    CA
;
run;

*Sort - required for both options;
proc sort data=have;
    by orgID;
run;

**********************************************************************;
*Use RETAIN and BY group processing to combine the information;
**********************************************************************;
data want_option1;
    set have;
    by orgID;
    length combined $100.;
    retain combined;

    if first.orgID then
        combined=states;
    else
        combined=catx(', ', combined, states);

    if last.orgID then
        output;
run;

**********************************************************************;
*Transpose it to a wide format and then combine into a single field;
**********************************************************************;
proc transpose data=have out=wide prefix=state_;
    by orgID;
    var states;
run;

data want_option2;
    set wide;
    length combined $100.;
    combined=catx(', ', of state_:);
run;

Re: How to CONCAN by Group in SAS

hellohere — Tue, 25 Jan 2022 01:26:10 GMT

thanks

Re: How to CONCAN by Group in SAS

s_lassen — Tue, 25 Jan 2022 10:40:12 GMT

The first solution suggested by @Reeza can be made a bit simpler (and slightly faster) like this:

data want_option1;
  do until(last.orgID);   
    set have;
    by orgID;
    length combined $100.;
    call catx(', ', combined, states);
    end;
run;

And it has to be done in a data step, because SAS SQL does not have concatenation as a "summary" function, unlike most other SQL dialects.

If performance matters, you should always use CALL CATX rather than the function.