topic Combining data from duplicative records in SAS Programming

Combining data from duplicative records

RandoDando — Tue, 25 Aug 2020 19:23:23 GMT

I feel as if I have done this before, so this will most likely serve as a refresher.

I have records identified here by "ID" which yield multiple records when joined with a "Type" field. See my hypothetical example below. Ideally, I want 1 row per "ID" with the different matching "types" concatenated and separated by a comma. See here:

What I have:

ID	Type
123	A1
456	B4
123	B4
567	B4
234	A1
456	A1
123	D2

What I want:

ID	Types
123	A1, B4, D2
234	A1
456	B4, A1
567	B4

Any idea how to create the table I want from a table such as the one given before it?

Re: Combining data from duplicative records

novinosrin — Tue, 25 Aug 2020 19:29:07 GMT

data have;
input (ID	Type) ($);
cards;
123	A1
456	B4
123	B4
567	B4
234	A1
456	A1
123	D2
;

proc sort data=have out=_have;
 by id type;
run;

data want;
 do until(last.id);
  set _have;
  by id;
  length types $100;
  types=catx(',',types,type);
 end;
 drop type;
run;

Re: Combining data from duplicative records

novinosrin — Tue, 25 Aug 2020 19:43:14 GMT


data have;
input (ID	Type) ($);
cards;
123	A1
456	B4
123	B4
567	B4
234	A1
456	A1
123	D2
;

data _null_;
 if _n_=1 then do;
  length types $100;
  dcl hash H () ;
  h.definekey  ("id") ;
  h.definedata ("id","types") ;
  h.definedone () ;
 end;
 do until(z);
  set have end=z;
  types=ifc(h.find()=0,catx(',',types,type),type);
  h.replace();
 end;
 if z;
 h.output(dataset:'want');
run;

Re: Combining data from duplicative records

novinosrin — Tue, 25 Aug 2020 19:52:38 GMT


data have;
input (ID	Type) ($);
cards;
123	A1
456	B4
123	B4
567	B4
234	A1
456	A1
123	D2
;
proc sql;
 create view temp as
 select distinct id, type
 from have;
quit;

proc transpose data=temp out=temp2;
 by id;
 var type;
run;

data want;
 set temp2;
 length types $100;
 types=catx(',',of col:);
 drop col: _:;
run;