topic Re: How to calculate sum of two consecutive numbers in a column using loops in sas? in New SAS User

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

Saurabh_Rana — Thu, 12 Mar 2020 07:04:29 GMT

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.

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

PeterClemmensen — Thu, 12 Mar 2020 07:31:03 GMT

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

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

ed_sas_member — Thu, 12 Mar 2020 07:31:13 GMT

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,

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

Saurabh_Rana — Thu, 12 Mar 2020 07:53:51 GMT

How can I do this using loops

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

PeterClemmensen — Thu, 12 Mar 2020 07:54:50 GMT

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

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

Kurt_Bremser — Thu, 12 Mar 2020 08:06:09 GMT

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

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

Saurabh_Rana — Thu, 12 Mar 2020 08:22:44 GMT

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

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

PeterClemmensen — Thu, 12 Mar 2020 08:26:27 GMT

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

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

Kurt_Bremser — Thu, 12 Mar 2020 08:29:10 GMT

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

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

Saurabh_Rana — Thu, 12 Mar 2020 08:37:58 GMT

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

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

Kurt_Bremser — Thu, 12 Mar 2020 09:03:10 GMT

@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)