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

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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