Adding multiple rows of data and display in one row based on ID

[West26]

Hello,

Struggling with the below. Basically I shouldn't be having multiple rows of same id. At the same time, their corresponding values of 'Desc' filed, has to be combined.

Data have:
ID        Desc

1234    AB
5678    CD
5678     EF
9999     GH

Data want:
ID       Desc
1234    AB
5678   CD_EF
9999    GH

[HB · Accepted solution]

data have;
input ID DESC $2.;
datalines;
1234 AB
5678 CD
5678 EF
9999 GH
run;

data want(keep=id list);
  do until (last.id);
    set have;
    by id;
    length list $30;    
    list = catx('_',list,desc);
  end;
run;

gives

ID	list
1234	AB
5678	CD_EF
9999	GH

Not my code.

https://communities.sas.com/t5/SAS-Programming/How-to-create-a-comma-separated-String-from-values-fr...

[West26]

Hello HB,

Thanks for taking your time and providing the solution. Appreciated.

[nikhilwagh]

Just another way --

data have;
input ID DESC $2.;
datalines;
1234 AB
5678 CD
5678 EF
9999 GH
run;

proc transpose data=have out=have_t(drop=_:);
by id;
var DESC;
run;

data want(drop=col:);
set have_t;
desc=catx("_", of col:);
run;