topic Re: How can I concatenate the data for each dept? in SAS Programming

How can I concatenate the data for each dept?

vishalrajpoot3 — Wed, 25 Jul 2018 18:03:34 GMT

Hello Team,

How can I concatenate the below data on the basis of unique dept with all the area in output?

data abc;
input dept $ area $;
datalines;
D1 a
d1 b
d1 c
d2 s
d2 d
d2 f
d3 y
d4 r
d4 f
d4 t
;
run;

Re: How can I concatenate the data for each dept?

PaigeMiller — Wed, 25 Jul 2018 18:08:09 GMT

Please show us the desired result.

Re: How can I concatenate the data for each dept?

novinosrin — Wed, 25 Jul 2018 18:12:01 GMT

something like this?

data abc;
input dept $ area $;
datalines;
D1 a
d1 b
d1 c
d2 s
d2 d
d2 f
d3 y
d4 r
d4 f
d4 t
;
run;

data want;
set abc;
by dept;
length new_var $30;
retain new_var;
if first.dept then call missing(new_var);
new_var=catx('-',new_var,area);
if last.dept;
run;

Re: How can I concatenate the data for each dept?

vishalrajpoot3 — Wed, 25 Jul 2018 18:36:57 GMT

@PaigeMiller

please see the below desired output.

dept area
D1 a, b, c
d2 s, d, f
d3 y
d4 r, f, t

Re: How can I concatenate the data for each dept?

novinosrin — Wed, 25 Jul 2018 18:39:25 GMT

Hi @vishalrajpoot3 Modified my code to your requirement

data temp;
set abc;
_dept=upcase(dept);
run;
data want;
set temp;
by _dept;
length new_var $30;
retain new_var;
if first._dept then call missing(new_var);
new_var=catx(',',new_var,area);
if last._dept;
run;

Re: How can I concatenate the data for each dept?

PaigeMiller — Wed, 25 Jul 2018 18:49:04 GMT

Thank you for showing us the desired output.

If you are creating a report and you need the results as shown, then I guess @novinosrin has given you the answer. However, if you are going to do additional analysis in SAS, this is a very poor way to structure the data for analysis, so I would advise against doing this in this case.

Re: How can I concatenate the data for each dept?

vishalrajpoot3 — Wed, 25 Jul 2018 19:06:17 GMT

so what would you like to suggest doing in this case?

Re: How can I concatenate the data for each dept?

PaigeMiller — Wed, 25 Jul 2018 19:08:35 GMT

It depends on what your next steps are.

Re: How can I concatenate the data for each dept?

Kurt_Bremser — Wed, 25 Jul 2018 19:09:38 GMT

@vishalrajpoot3 wrote:
so what would you like to suggest doing in this case?

Very simple. Don't do it. Longitudinal datasets are always better for analysis. Wide formats are only needed for presentations to PHBs.