Hi, am wondering if i could please get some help here...
I have a table with duplicate rows for ID. I wanted to loop through column ID, if there are multiple records for the same ID then sum the value.
ID Value
1 5
1 10
2 50
3 5
3 10
3 10
So the resulting table should look like below. I am not sure how to go about it. Can anyone help please?
ID Value
1 15
2 50
3 25
Many thanks in advance!
Michelle
Please try
data have;
input ID Value;
cards;
1 5
1 10
2 50
3 5
3 10
3 10
;
proc sql;
create table want as select id, sum(value) as value from have group by id;
quit;
Please try
data have;
input ID Value;
cards;
1 5
1 10
2 50
3 5
3 10
3 10
;
proc sql;
create table want as select id, sum(value) as value from have group by id;
quit;
by data step
data want;
set have(rename=(value=_value));
retain value;
by id;
if first.id then value=_value;
else value+_value;
if last.id;
drop _value;
run;
Hello Jag, thanks very much! I’ve used your solution and it absolutely worked on my dataset. I thought I had learn about loops but this turns out to be much better.
Thanks again.
Michelle
A Proc Report would also be fine;
proc report data=have nowd out=want (drop=_break_ );
column ID_ value;
define ID_ / group;
run;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.