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;

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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