BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
topryde
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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; 
Thanks,
Jag

View solution in original post

4 REPLIES 4
Jagadishkatam
Amethyst | Level 16

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; 
Thanks,
Jag
Jagadishkatam
Amethyst | Level 16

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; 

 

Thanks,
Jag
topryde
Fluorite | Level 6

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

 

nketata
Obsidian | Level 7

A Proc Report would also be  fine;

 

proc report data=have nowd out=want (drop=_break_ );

column ID_ value;

define ID_ / group;

run;

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 2584 views
  • 1 like
  • 3 in conversation