topic Re: How to use Do Loop to concate email addresses into single value in SAS Programming

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

jlwatts — Wed, 24 Jan 2024 19:29:19 GMT

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;

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

Kurt_Bremser — Wed, 24 Jan 2024 19:50:45 GMT

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;

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

ballardw — Wed, 24 Jan 2024 21:13:50 GMT

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;

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

Tom — Thu, 25 Jan 2024 03:27:28 GMT

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.

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

Kurt_Bremser — Thu, 25 Jan 2024 07:25:47 GMT

Correct. Missed the RETAIN.