concatenate the values

[nickspencer]

Hi All,

I have a dataset with an ID and an amount columns. Some of the IDs are duplicate with the different amounts. I want to concatenate the amounts and only keep the first record. If n>1, then want to concatenate the amount values but keep other values from the record where n=1. How can I achieve that?

Have:

ID        Amount   Date              Time    n

123      200        1/1/2020         3:50   1

456      500        1/3/2020         2:30    1

456      200        1/5/2020         1:45    2

456      600        1/8/2020         2:35    3

980      400        1/9/2020         4:30    1

Want:

ID          Amount                Date                      Time

123       200                        1/1/2020               3:50

456       500,200,600           1/3/2020              2:30

980      400                          1/9/2020              4:30

Thank you

[Reeza]

This shows two different methods to accomplish this. 

Given you have other variables you want to maintain, I'd recommend the data step approach. https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

---

@nickspencer wrote:

Hi All,

I have a dataset with an ID and an amount columns. Some of the IDs are duplicate with the different amounts. I want to concatenate the amounts and only keep the first record. If n>1, then want to concatenate the amount values but keep other values from the record where n=1. How can I achieve that?

Have:

ID        Amount   Date              Time    n

123      200        1/1/2020         3:50   1

456      500        1/3/2020         2:30    1

456      200        1/5/2020         1:45    2

456      600        1/8/2020         2:35    3

980      400        1/9/2020         4:30    1

Want:

ID          Amount                Date                      Time

123       200                        1/1/2020               3:50

456       500,200,600           1/3/2020              2:30

980      400                          1/9/2020              4:30

Thank you

---

[novinosrin]

HI @nickspencer  Been a while since i used curobs options. This case seems to present that possibility

data have;
input (ID        Amount   Date              Time ) ($)   n;
cards;
123      200        1/1/2020         3:50   1

456      500        1/3/2020         2:30    1

456      200        1/5/2020         1:45    2

456      600        1/8/2020         2:35    3

980      400        1/9/2020         4:30    1
;

data want;
 do until(last.id);
  set have curobs=k;
  by id;
  length _amount $100;
  _amount=catx(',',_amount,amount);
  if first.id then _n_=k;
 end;
 set have point=_n_;
run;

[Kurt_Bremser]

data want;
set have (rename=(amount=_amount date=_date time=_time));
by id;
retain amount date time;
format
  date mmddyy10.
  time time5.
;
length amount $30;
if first.id
then do;
  amount = _amount;
  date = _date;
  time = _time;
end;
else amount = catx(',',amount, _amount);
drop _:;
if last.id;
run;