topic Re: Adding multiple rows of data and display in one row based on ID in SAS Programming

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

West26 — Fri, 03 Jun 2022 17:47:52 GMT

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

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

HB — Fri, 03 Jun 2022 20:25:08 GMT

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-from-a/td-p/646925

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

West26 — Sat, 04 Jun 2022 11:38:26 GMT

Hello HB,

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

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

nikhilwagh — Sat, 04 Jun 2022 15:19:30 GMT

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;