topic concatenate the values in SAS Programming

concatenate the values

nickspencer — Fri, 27 Mar 2020 17:41:05 GMT

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

Re: concatenate the values

Reeza — Fri, 27 Mar 2020 17:47:37 GMT

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

Re: concatenate the values

novinosrin — Fri, 27 Mar 2020 18:11:40 GMT

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;

Re: concatenate the values

Kurt_Bremser — Sat, 28 Mar 2020 07:02:05 GMT

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;