topic Re: Merging row text by ID in SAS Programming

Merging row text by ID

rajd1 — Wed, 01 Jul 2020 20:23:32 GMT

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,

Re: Merging row text by ID

novinosrin — Wed, 01 Jul 2020 20:33:36 GMT


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;

Re: Merging row text by ID

Sathish_jammy — Wed, 01 Jul 2020 20:40:47 GMT

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;

Re: Merging row text by ID

rajd1 — Wed, 01 Jul 2020 20:41:55 GMT

Thank you Sir

Re: Merging row text by ID

rajd1 — Wed, 01 Jul 2020 20:42:09 GMT

Thanks a lot!

Re: Merging row text by ID

rajd1 — Wed, 01 Jul 2020 22:01:47 GMT

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'

Re: Merging row text by ID

novinosrin — Wed, 01 Jul 2020 22:43:30 GMT

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;