topic Re: How to: Make a new column with summation of a series? in SAS Programming

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

raghavtrip — Wed, 22 Sep 2021 22:42:01 GMT

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!

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

Reeza — Wed, 22 Sep 2021 22:40:53 GMT

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.

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

raghavtrip — Wed, 22 Sep 2021 22:44:34 GMT

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)

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

Reeza — Wed, 22 Sep 2021 23:18:41 GMT

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.

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

Reeza — Wed, 22 Sep 2021 23:30:26 GMT

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;

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

raghavtrip — Thu, 23 Sep 2021 00:13:47 GMT

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!

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

raghavtrip — Thu, 23 Sep 2021 00:33:56 GMT

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?

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

Astounding — Thu, 23 Sep 2021 09:22:27 GMT

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.

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

Reeza — Thu, 23 Sep 2021 14:34:27 GMT

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.