BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Krabkvadrat
Fluorite | Level 6

Hello everyone

Really need community help!

 

I have a dataset with emails like this

 

name        email

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

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,

View solution in original post

5 REPLIES 5
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
ed_sas_member
Meteorite | Level 14

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,

Krabkvadrat
Fluorite | Level 6
Thank you, it worked!
ed_sas_member
Meteorite | Level 14
You're welcome !

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1525 views
  • 2 likes
  • 3 in conversation