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;
April 27 – 30 | Gaylord Texan | Grapevine, Texas
Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and lock in 2025 pricing—just $495!
Still thinking about your presentation idea? The submission deadline has been extended to Friday, Nov. 14, at 11:59 p.m. ET.
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.