BookmarkSubscribeRSS Feed
RandoDando
Pyrite | Level 9

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?

 

3 REPLIES 3
novinosrin
Tourmaline | Level 20
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;
novinosrin
Tourmaline | Level 20

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;
novinosrin
Tourmaline | Level 20

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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 633 views
  • 0 likes
  • 2 in conversation