Hello everyone
Really need community help!
I have a dataset with emails like this
name | |
John | John@mail.com |
Alex | Alex@mail.com |
Mary | Mary@mail.com |
What I need is to somehow create table/dataset like this
So to accumulate emails in a single cell.
emails |
Josh@mail.com Alex@mail.com Mary@mail.com |
Afterwards need to create a variable, in order to use it in my emailing program
FILENAME sending EMAIL TO = &emails .... etc
Thank you in advance!
Hi @Krabkvadrat
You can get the macro variable in a single step without creating a new variable:
data have;
input name $ email:$50.;
datalines;
John John@mail.com
Alex Alex@mail.com
Mary Mary@mail.com
;
run;
proc sql;
select email into:email separated by " " from have;
run;
%put &email.; John@mail.com Alex@mail.com Mary@mail.com
If you want to create a table you can use a proc transpose and then concatenate the variables:
data have;
input name $ email:$50.;
datalines;
John John@mail.com
Alex Alex@mail.com
Mary Mary@mail.com
;
run;
proc transpose data=have out=have_tr (drop=_:);
var email;
run;
data want;
set have_tr;
length email $ 500;
email = catx(" ",of col:);
keep email;
run;
proc sql;
select email into:email from want;
run;
Best,
Please try the below code
data have;
input name$ email$30.;
ord=1;
cards;
John John@mail.com
Alex Alex@mail.com
Mary Mary@mail.com
;
proc sort data=have;
by ord name;
run;
data want;
length emails $200.;
set have;
by ord name;
retain emails;
if first.ord then emails=email;
else emails=catx(' ',emails,email);
if last.ord;
run;
proc sql;
select emails into: emails from want;
quit;
%put &emails;
filename mailbox email "&emails" subject='emails';
data _null_;
file mailbox;
put 'hello';
run;
Hi @Krabkvadrat
You can get the macro variable in a single step without creating a new variable:
data have;
input name $ email:$50.;
datalines;
John John@mail.com
Alex Alex@mail.com
Mary Mary@mail.com
;
run;
proc sql;
select email into:email separated by " " from have;
run;
%put &email.; John@mail.com Alex@mail.com Mary@mail.com
If you want to create a table you can use a proc transpose and then concatenate the variables:
data have;
input name $ email:$50.;
datalines;
John John@mail.com
Alex Alex@mail.com
Mary Mary@mail.com
;
run;
proc transpose data=have out=have_tr (drop=_:);
var email;
run;
data want;
set have_tr;
length email $ 500;
email = catx(" ",of col:);
keep email;
run;
proc sql;
select email into:email from want;
run;
Best,
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.