Hi, I am working with some textual data and i want to combine textual rows by ID.
Data have:
ID | alpha |
1 | a |
1 | b |
1 | c |
2 | a |
2 | c |
3 | a |
3 | b |
4 | a |
Data Want:
1 | a,b,c |
2 | a,c |
3 | a,b |
4 | a |
Thanks,
data have;
input ID alpha $;
cards;
1 a
1 b
1 c
2 a
2 c
3 a
3 b
4 a
;
data want;
do until(last.id);
set have;
by id;
length want $100;
want=catx(',',want,alpha);
end;
drop alpha;
run;
data have;
input ID alpha $;
cards;
1 a
1 b
1 c
2 a
2 c
3 a
3 b
4 a
;
data want;
do until(last.id);
set have;
by id;
length want $100;
want=catx(',',want,alpha);
end;
drop alpha;
run;
data have;
input ID alpha $;
cards;
1 a
1 b
1 c
2 a
2 c
3 a
3 b
4 a
4 a
;
data want;
do until(last.id);
set have;
by id;
length want $100;
want=catx(',',want,alpha);
end;
drop alpha;
run;
Just a minor addition: What if i had the same alpha for the same id. For example id#4 has a and a twice. I just want it to be displaced as 'a' instead of 'a,a'
Hi @rajd1 Please try the modified and let me know-
data have;
input ID alpha $;
cards;
1 a
1 b
1 c
2 a
2 c
3 a
3 b
4 a
4 a
;
data want;
do until(last.id);
set have;
by id;
length want $100;
if ^index(want,strip(alpha)) then want=catx(',',want,alpha);
end;
drop alpha;
run;
The following code will help you.
data want(Keep = id newvar); length newvar $200.; set have; by ID; retain newvar; if first.ID then newvar=alpha; else newvar=catx(',',newvar,alpha); if last.ID; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.