How can I concatenate the data for each dept?

[vishalrajpoot3]

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;

[PaigeMiller]

Please show us the desired result.

[vishalrajpoot3]

@PaigeMiller

please see the below desired output.

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

[PaigeMiller]

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.

[vishalrajpoot3]

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

[PaigeMiller]

It depends on what your next steps are.

[Kurt_Bremser]

---

@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.

[novinosrin]

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;

[novinosrin]

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;