topic Re: Concatenation of vertical rows into one cell in SAS Programming

Concatenation of vertical rows into one cell

Krabkvadrat — Mon, 20 Apr 2020 10:04:25 GMT

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!

Re: Concatenation of vertical rows into one cell

Jagadishkatam — Mon, 20 Apr 2020 10:19:49 GMT

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;

Re: Concatenation of vertical rows into one cell

ed_sas_member — Mon, 20 Apr 2020 10:27:27 GMT

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,

Re: Concatenation of vertical rows into one cell

Krabkvadrat — Mon, 20 Apr 2020 10:33:31 GMT

Thank you!

Re: Concatenation of vertical rows into one cell

Krabkvadrat — Mon, 20 Apr 2020 10:33:50 GMT

Thank you, it worked!

Re: Concatenation of vertical rows into one cell

ed_sas_member — Mon, 20 Apr 2020 10:40:09 GMT

You're welcome !