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?
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;
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;
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;
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!
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.
Ready to level-up your skills? Choose your own adventure.