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;

[dxiao2017]

Hi @pavank does this answer your question? My code and result is as follows (the code I take as my reference code is in SAS Macro1: essentials course note pdf):

data employees;
   input department $ employee $;
   datalines;
Sales John
Sales Mary
HR Alice
HR Bob
IT Eve
IT Charlie
;
run;
proc sql;
select distinct department
   into :deptlist1-
   from employees;
quit;
%put &sqlobs;/*3*/
%put &deptlist1 &deptlist2 &deptlist3;
%macro emp;
%do i=1 %to 3;
proc sql noprint;
select distinct employee
   into :emplist separated by ','
   from employees
   where department="&&deptlist&i";
quit;
%put &emplist;
%end;
%mend emp;
%emp;

 69         data employees;
 70            input department $ employee $;
 71            datalines;
 
 NOTE: The data set WORK.EMPLOYEES has 6 observations and 2 variables.

 78         ;
 79         run;
 80         proc sql;
 81         select distinct department
 82            into :deptlist1-
 83            from employees;
 84         quit;
 NOTE: PROCEDURE SQL used (Total process time):

 85         %put &sqlobs;/*3*/
 3
 86         %put &deptlist1 &deptlist2 &deptlist3;
 HR IT Sales
 87         %macro emp;
 88         %do i=1 %to 3;
 89         proc sql noprint;
 90         select distinct employee
 91            into :emplist separated by ','
 92            from employees
 93            where department="&&deptlist&i";
 94         quit;
 95         %put &emplist;
 96         %end;
 97         %mend emp;
 98         %emp;
 NOTE: PROCEDURE SQL used (Total process time):

 Alice,Bob
 NOTE: PROCEDURE SQL used (Total process time):

 Charlie,Eve
 NOTE: PROCEDURE SQL used (Total process time):

 John,Mary
 99         
 100        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

[Tom]

That appears to be an exercise to show some of the features of the INTO keyword in PROC SQL.

I would NOT recommend using that method in real code unless absolutely forced to by some strange circumstances. 

In general it is better to keep data in data and not transfer it to strings so you can store it in macro variables.

[dxiao2017]

Hi @Tom thanks a lot for your reply and comments! This is what I think:

1) My answer was not an exercise of select into :list; statement. I wrote "I took example in SAS Macro1: essentials course note pdf as my reference code", by that I mean I learnt the select into :list statement and technique from the material, I was not saying all parts of my answer was from the material.

2) I came up with my solution like this:

2.1) The original post asks "how to get a comma separated employee list using proc sql?" This is a typical question that can be solved through proc sql; select into :list statement.

2.2) the original post asks "how to get a comma separated employee list for each department?" To answer this part of the question, one need to add a where; statement in the proc sql; select into :list; step, and the where statement specifies something like where deptvar="deptmentname" ;. One can repeat the proc sql; select into :list  step several times and use different department values in the where; statement. Since the step need to be repeated, creating macro variables that has a list of values for the department is a proper method to use, and this leads to another proc sql; select into :list step, which creates the department value list. One can create the macro variable list through data _null_; and call symputx();, but the proc sql; select into :list came to my mind first so I used that one.