topic SAS PROC SQL to loop through a column in SAS Procedures

SAS PROC SQL to loop through a column

topryde — Mon, 21 Dec 2015 06:05:56 GMT

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

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

Re: SAS PROC SQL to loop through a column

Jagadishkatam — Mon, 21 Dec 2015 06:21:27 GMT

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;

Re: SAS PROC SQL to loop through a column

Jagadishkatam — Mon, 21 Dec 2015 06:22:28 GMT

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;

Re: SAS PROC SQL to loop through a column

topryde — Mon, 21 Dec 2015 22:00:48 GMT

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

Re: SAS PROC SQL to loop through a column

nketata — Mon, 04 Jan 2016 19:35:19 GMT

A Proc Report would also be fine;

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

column ID_ value;

define ID_ / group;

run;