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
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
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;
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;
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.