How to use Do Loop to concate email addresses into single value

[jlwatts]

I have a table (email_list) with two values:

• ORG_ID
• email_address

This tables has multiple records with the same ORG_ID and unique email addresses.  I want to build a table so that I have a single row for each ORG_ID with all of the individual emails concatenated into a single value with the emails separated by a comma so that I can use that field to email the users.

I have the count of the records:

proc sql;

   select count(*) into :rec_count

   from work.email_list;

quit;

Then I want to build the list:

%do j=1 to &rec_count;

    if first record:

        org = org_id;

        email = email_address

    else

       if org = org_id then email = email || "', '" || email_address

       else

           org = org_id,

           email = email_address;

[Kurt_Bremser]

You don't need the record count.

proc sort data=have;
by org_id;
run;

data want (compress=yes);
set have;
by org_id;
length email $32767; /* maximum possible length */
if first.org_id
then email = email_address;
else email = catx(",",email,email_address);
if last.org_id;
drop email_address;
run;

[ballardw]

Did we perhaps miss a Retain?

data want (compress=yes);
set have;
by org_id;
length email $32767; /* maximum possible length */
retain email;
if first.org_id
then email = email_address;
else email = catx(",",email,email_address);
if last.org_id;
drop email_address;
run;

[Kurt_Bremser]

Correct. Missed the RETAIN.

[Tom]

Put the SET statement inside the DO loop.

data want;
  do until(last.org_id);
    set have;
    by org_id;
    length email $1000;
    email=catx(',',email,email_address);
  end;
  drop email_address;
run;

PS Don't try to use macro code to generate SAS code until you know what SAS code you need to generate.  For this problem there is no need to generate any code, so macro code is not needed.