comma separated list using proc sql

[pavank]

data employees;
    input department $ employee $;
    datalines;
Sales John
Sales Mary
HR Alice
HR Bob
IT Eve
IT Charlie
;
run;


proc sql;
    select department,
           catx(',', employee) as employees
    from
        select distinct department,
               employee
        from employees
    ) group by employee,department;
quit;

how to get comma separated employees list for each department using proc sql

[ChrisNZ]

There's no such SQL function in SAS sadly.

See

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

https://communities.sas.com/t5/SASware-Ballot-Ideas/Add-GROUP-CONCAT-function-to-proc-sql/idi-p/3230...

[PaigeMiller]

Please tell us what you would do with these comma separated lists by department, so that we may be able to come up with another method that doesn't involve SQL.

[Kurt_Bremser]

In SAS, you do this with a DATA step, not with SQL.
See Maxim 14.

[yabwon]

First remark: Maxim 14: Use the right tool.

Do it the SAS way:

data employees;
    input department $ employee $;
    datalines;
Sales John
Sales Mary
HR Alice
HR Bob
IT Eve
IT Charlie
IT Zelda
;
run;

data Want;
  set employees;
  by department notsorted;
  length list $ 100;
  retain list;
  if first.department then list="";

  list = catx(",",list,employee);

  if last.department;
  keep department list;
run;
proc print data=want;
run; 

Second remark:

@ChrisNZ : "You can't do it in SAS",

Me: "Hold my beer" 😉 😉

Assuming you know the maximum number of employees in a department (and you can count that) you could try this:

data employees;
    input department $ employee $;
    datalines;
Sales John
Sales Mary
HR Alice
HR Bob
IT Eve
IT Charlie
IT Zelda
;
run;

data Want;
  set employees;
  by department notsorted;
  length list $ 100;
  retain list;
  if first.department then list="";

  list = catx(",",list,employee);

  if last.department;
  keep department list;
run;
proc print;
run; 

proc sql;
select max(m)
into :maxDept /* max number of people in a the department */
from
(select count(department) as m 
 from employees
 group by department
)
;

create table wantSQL_base as
  select distinct department, employee, count(employee) as n
  from
  (
    select a.*
    from employees as a
    join employees as b
    on a.department = b.department
    and a.employee<=b.employee
  )
  group by department, employee
;

create table wantSQL_final as
  select distinct  
  t1.department, catx(","
  %macro loop1(n);
    %do n=1 %to &n;
      ,t&n..employee
    %end;
  %mend;
  %loop1(&maxDept.)
  ) as list
  from
    %macro loop2(n);
    %do n=1 %to &n;
      %if &n>1 %then left join;
      (select department, employee from wantSQL_base where n=&n.) as t&n.
      %if &n>1 %then on t&n..department = t%eval(&n.-1).department;
    %end;
    %mend;
    %loop2(&maxDept.)
;
quit;


proc print data=wantSQL_base;
run; 
proc print data=wantSQL_final;
run; 

(But remember the fact you CAN do something does not mean you SHOULD do it!)

Bart

[Kurt_Bremser]

I want to suggest a small optimization to your code:

data Want;
  set employees;
  by department notsorted;
  length list $ 100;
  retain list;
  if first.department
  then list = employee;
  else list = catx(",",list,employee);
  if last.department;
  keep department list;
run;

This way you do only one assignment at FIRST.

[yabwon]

Good point! Thanks Kurt (@Kurt_Bremser)

Bart

[ChrisNZ]

---

@ChrisNZ : "You can't do it in SAS",

---

I only said SQL can't do it in SAS, not that SAS can't do it.

You are (cleverly!) supplementing the limitation of SQL by adding a layer of macro language logic, but the limitation remains.

[yabwon]

You right, the limitation remains, that's why I'm fully support (2x👍) those ballot ideas you shared. Unfortunately, I'm afraid that SAS (the firm) won't be very eager to add new features to SAS (language and 9 engine)... With all that "Viya-centrism"... 😞

But hopefully Workbench will give more drive to make some good BASE engine extensions. 

Bart

[ChrisNZ]

There's also this SQL improvement of you feel like voting more
https://communities.sas.com/t5/SASware-Ballot-Ideas/Add-window-functions-in-SAS-SQL/idi-p/462556

[FreelanceReinh]

Here's another just-for-fun approach, suitable (without further additions) only for small examples like your sample data or sashelp.class, if any:

proc sql noprint;
select catx(':',department,employee,department) into :x separated by '|'
from employees
order by department, employee;

create table want as
select department, scan(substr(tranwrd("&x",cats(':',department,'|',department,':'),', '),find("&x",cats(department,':'))),2,':') as emp_list
from (select distinct department from employees);
quit;

[Tom]

That is not something that SQL is designed to support since it does not look like set operations (or relational algebra).  I know that many databases that use SQL as their query language have enhanced the SQL language to handle such things.  But that is because they did not already have a data manipulation language, like SAS does.

I note that your posted data is grouped by department already (but not sorted) so you could process it as is by using the NOTSORTED keyword on the BY statement.  But you probably want to first SORT by the grouping variable (DEPARTMENT).  You might also want to sort within the grouping variable by some other variable that makes sense.  Perhaps seniority in the department? Or in the company? Or just alphabetically by name.  

You will also need to decide how long this new character variable needs to be to hold the longest possible list of employees.

proc sort data=employees ;
  by department employee;
run;
data want ;
  do until(last.department);
     set employees;
     by department employee;
     length employees $200 ;
     employees = catx(',',employees,employee);
  end;
  keep department employees ;
run;