Help using Base SAS procedures

SAS PROC SQL to loop through a column

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

SAS PROC SQL to loop through a column

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


Accepted Solutions
Solution
‎12-21-2015 05:00 PM
Trusted Advisor
Posts: 1,128

Re: SAS PROC SQL to loop through a column

[ Edited ]

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


All Replies
Solution
‎12-21-2015 05:00 PM
Trusted Advisor
Posts: 1,128

Re: SAS PROC SQL to loop through a column

[ Edited ]

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
Trusted Advisor
Posts: 1,128

Re: SAS PROC SQL to loop through a column

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
New Contributor
Posts: 2

Re: SAS PROC SQL to loop through a column

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

 

Contributor
Posts: 34

Re: SAS PROC SQL to loop through a column

A Proc Report would also be  fine;

 

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

column ID_ value;

define ID_ / group;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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