How to calculate sum of two consecutive numbers in a column using loops in sas?

[Saurabh_Rana]

I want to find sum of 2 consecutive numbers or 3 consecutive numbers group wise in a column as shown in the example below:-

Example- Sum of 2 consecutive numbers

Input Data

A 1

A 2

A 3

A 4

A 5

B 1

B 3

B 5

B 7

B 9

B 11

Output

A 3

A 5

A 7

A 9

A 5

B 4

B 8

B 12

B 16

B 20

B 11

The last observation in each group is as it is since it is the last number.

[PeterClemmensen]

Here is one way

data have;
input id $ num;
datalines;
A 1
A 2
A 3
A 4
A 5
B 1
B 3
B 5
B 7
B 9
B 11
;

data want (keep=id sum);
    merge have
          have (firstobs=2 rename=(num=_num id=_id));
    if id=_id then sum=num+_num;
    else sum=num;
run;

Result:

id sum 
A  3 
A  5 
A  7 
A  9 
A  5 
B  4 
B  8 
B  12
B  16
B  20
B  11 

[ed_sas_member]

Hi @Saurabh_Rana 

data have;
	Input v1 $ v2;
	datalines;
A 1
A 2
A 3
A 4
A 5
B 1
B 3
B 5
B 7
B 9
B 11
;
run;

data want;
	merge have have(firstobs=2 rename=(V1=_V1 V2=_V2));
	if V1=_V1 then v2_sum = V2+_V2;
	else v2_sum = V2;
	drop _: V2;
run;

Best,

[Saurabh_Rana]

How can I do this using loops

[PeterClemmensen]

@Saurabh_Rana why do you want to use a loop to do this? 

[Kurt_Bremser]

---

@Saurabh_Rana wrote:

How can I do this using loops

---

You already use a loop: the implicit loop that the data step does over the observations in the input dataset(s). Your question therefore makes no sense.

Please mark one of the answers (both do what you want) as the solution.

[Saurabh_Rana]

How can I do the same process for sum of 3 consecutive numbers?

[PeterClemmensen]

Do you want a general solution that handles n consecutive numbers or is three your actual goal? 

[Saurabh_Rana]

Yes I want a general solution that handles n consecutive numbers.

[Kurt_Bremser · Accepted solution]

---

@Saurabh_Rana wrote:

Yes I want a general solution that handles n consecutive numbers.

---

Then let's start with code that does it (Rule #1 of macro development) and see what needs to be dynamic:

data want;
merge
  have
  have (firstobs=2 rename=(V1=_V1_1 V2=_V2_1))
  have (firstobs=3 rename=(V1=_V1_2 V2=_V2_2))
;
v2_sum = V2;
if V1 = _V1_1
then v2_sum = v2_sum +_V2_1;
if V1 = _V1_2
then v2_sum = v2_sum +_V2_2;
drop _:;
run;

This code solves the "sum for three/double look-ahead" issue. Since I already wrote it in a fashion that makes repeating code parts visible (that's one reason why proper code formatting is an absolute MUST for anyone wanting to be called a "coder"!!!), it is not that hard to create a macro loop that repeats that code:

%macro repeat(count=0);
%local i;
data want&count;
merge
  have
%do i = 1 %to &count;
  have (firstobs=%eval(&i + 1) rename=(V1=_V1_&i V2=_V2_&i))
%end;
;
v2_sum = V2;
%do i = 1 %to &count;
if V1 = _V1_&i
then v2_sum = v2_sum +_V2_&i;
%end;
drop _:;
run;
%mend;
%repeat(count=1)
%repeat(count=2)

[Kurt_Bremser]

---

@Saurabh_Rana wrote:

How can I do the same process for sum of 3 consecutive numbers?

---

You just need to use a second lookahead with firstobs=3, and expand the logic in the data step accordingly. Try your hand at the code you already got, and if you run into problems, post the code and the log, so we can help you fixing it. Learn by doing.