How to: Make a new column with summation of a series?

[raghavtrip]

Hi everyone,

Essentially, I need to make a new column with a summation of a few other variables--here's a mathematical expression of the new variable I need to make. VarA and VarB are continuous variables present in other rows. I'm basically trying to make a new column in my dataset with the value given by the following equation:

What code can I write to make a new column with the results of this summation, given that I already have an integer value in each row for columns VarA and VarB?

I googled this and tried using a "do loop," but it hasn't been working. Here's what I've tried:

data want(drop=t);
set have;
do t=1 to t=VarA;
sum + (1-VarB/((1.03)**(t-1));
end;
run;

To make it clearer, an example of my larger dataset is below:

ID     VarA     VarB

1        7            0.3

2        9            0.4

3        4            0.2

4        12          0.3

5        16          0.2

I'm trying to make a new column with values populated by the summation equation given above.

Please let me know if I can provide any additional information and I'm happy to do so, been stuck on this all week!

[Reeza]

Please show an example of what your input data looks like. 

Ideally, you can show a small sample with the expected output. 

Data step loops automatically so you don't need to loop at all usually. 

[raghavtrip]

Thanks for your response! Here's what my data are formatted like:

ID     VarA     VarB

1        7            0.3

2        9            0.4

3        4            0.2

4        12          0.3

5        16          0.2

Expected output would be a data file with a new column given by the summation equation given above. So, in other words, I currently have the data below:

ID     VarA     VarB

1        7            0.3

2        9            0.4

3        4            0.2

4        12          0.3

5        16          0.2

I want to make a new file with the data below:

ID     VarA     VarB    SUMMED_VARS

1        7            0.3   (result of summation)

2        9            0.4   (result of summation)

3        4            0.2   (result of summation)

4        12          0.3   (result of summation)

5        16          0.2   (result of summation)

[Reeza]

I was asking for the actual value so that we can ensure were interpreting the formula correctly, not just result of the summation. For example, is it the same value in all rows or is it cumulative for each row.

[Reeza]

1. Assumes you want the same value in each row

2. Assumes that you do not need to do this for any By groups. 

data have;
input ID     VarA     VarB;
cards;
1        7            0.3
2        9            0.4
3        4            0.2
4        12          0.3
5        16          0.2
;;;;
run;



data summary;
set have end=eof;
retain running_total;
term = (1 - varb)/ (1.03 ** (VarA-1));
running_total + term;
if eof then output;
keep running_total;
run;

data want;
set have;
if _n_ =1 then set summary;
run;

[raghavtrip]

Thanks for the help! I'm actually hoping to have a different value in each row (cumulative for each row). So let's say the following is my input data:

data have;
input ID     VarA     VarB;
cards;
1        7            0.3
2        9            0.4
3        4            0.2
4        12          0.3
5        16          0.2
;;;;
run;

I want my output data to have a new column with the resultant value in each row being the result of the sum function I had copied above. This would yield the following output (I calculated the results manually):

ID     VarA     VarB    SUMMED_VARS

1        7            0.3    1.0495

2        9            0.4   0.89995

3        4            0.2   1.185

4        12          0.3   1.049

5        16          0.2   1.1999

Do you know if there's any way to do this? Thanks again in advance for all your help!

[raghavtrip]

Thanks for your help! I actually need a unique value in each row--basically, I need a summation calculated for the row and I want a new column with all the summations for each row. In other words, here is the data I have:

ID     VarA     VarB    

1        7            0.3 

2        9            0.4  

3        4            0.2  

4        12          0.3  

5        16          0.2 

And here is the data I want (a new column with new sums for each row using the equation above):

ID     VarA     VarB    SUMMED_VARS

1        7            0.3   4.492

2        9            0.4   4.811

3        4            0.2   3.062

4        12          0.3   7.177

5        16          0.2   10.350

Is there any way to do this, effectively running a different summation for each row using VarA and VarB?

[Astounding]

Does your current program work properly for the first row? If so, you are missing just one key step. Just before the DO loop, add:

sum = 0;

Otherwise SUM becomes a cumulative total across observations.

[Reeza · Accepted solution]

data want;
set have;

running_total = 0;

do i=1 to varA;
	running_total = running_total + (1-varb)/(1.03**(varb-1));
end;

run;

Then your original code was very close.