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!
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.
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.
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)
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;
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!
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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.