I want to create a data set that calculates a variable, ADJ_PIA, as PIA*(1+COLA) for observation 1. Then, for subsequent observations, the calculation should be ADJ_PIA*(1+COLA). Basically, each year the ADJ_PIA from the previous year should increase by the COLA percent. The COLA is currently 2%, but could become variable later on.
Here is how the observations should look. It represents a 2% increase over the previous value each time:
YEAR | PIA | COLA | ADJ_PIA |
2018 | $1,000.00 | 0 | $1,000.00 |
2019 | $1,000.00 | 2% | $1,020.00 |
2020 | $1,000.00 | 2% | $1,040.40 |
2021 | $1,000.00 | 2% | $1,061.21 |
2022 | $1,000.00 | 2% | $1,082.43 |
This is the first code I tried. It's been pointed out that this is not valid.
if year=2018 then ADJ_PIA=PIA;
else ADJ_PIA*(1+COLA);
Next code I tried:
ADJ_PIA=PIA*(1+COLA);
LAG_ADJ_PIA = LAG(ADJ_PIA);
ADJ_PIA2=LAG(ADJ_PIA)*(1+COLA);
This one doesn't have an error because it's valid. It just isn't what I want to happen.
I don't even know how to search for what I want to do because I don't have a name for this process.
Any help is greatly appreciated.
It is really not clear what data you are starting with.
Let's assume you have this trivial dataset.
data have ;
year=2018;
pia=1000;
cola=0.02 ;
run;
Now if you want expand that to add 5 more years you could just run this simple data step.
data want ;
set have ;
output;
do year=year+1 to year+5 ;
pia = pia*(1+cola);
output;
end;
run;
result
Obs year pia cola 1 2018 1000.00 0.02 2 2019 1020.00 0.02 3 2020 1040.40 0.02 4 2021 1061.21 0.02 5 2022 1082.43 0.02 6 2023 1104.08 0.02
The statement you have after ELSE is not a valid SAS statement. Don't you want an assignment statement there?
Don't you want an assignment statement there?
I have not worked with assignment statements. I will research and try this out.
@kharrigan wrote:
Don't you want an assignment statement there?I have not worked with assignment statements. I will research and try this out.
Your current THEN clause has a valid assignment statement.
This was completely correct. I fixed the else statement and it worked exactly as I wanted it to. Thank you for your help.
data Benefit_Amounts_PIA;
do year=2018 to 2050;
PIA=1000;
IF YEAR=2018 THEN COLA= .; ELSE COLA=.02;
if year=2018 then ADJ_PIA=PIA;
else adj_pia=ADJ_PIA*(1+COLA);
output;
end;
run'
@kharrigan wrote:
I've attached an Excel sheet of what I'm trying to do because I cannot figure out how upload my actual SAS dataset, and honestly, it's a huge mess right now.
Why is it easier to type 20 numbers into a spreadsheet than to type them into a program? Or just into the forum. Heck if you already have them in a spreadsheet then just copy and paste them into the forum editor.
I apologize. I didn't realize I could just copy and paste from Excel into the text box. I've updated my post.
Avoid MS files please. Many people can't/won't download them.
This method is the way to do what you want. Why won't you use it?
LAG_ADJ_PIA = LAG(ADJ_PIA);
ADJ_PIA2=LAG_ADJ_PIA*(1+COLA);
I will update my original question to be more clear without including an Excel file.
I don't use it because it doesn't work. This is the result I get when I run that code:
I want one variable to calculate a 2% increase over the previous value. So, for example, the observation values should be
$1,000.00 |
$1,020.00 |
$1,040.40 |
$1,061.21 |
$1,082.43 |
This would show a 2% increase over the previous value each time.
It is really not clear what data you are starting with.
Let's assume you have this trivial dataset.
data have ;
year=2018;
pia=1000;
cola=0.02 ;
run;
Now if you want expand that to add 5 more years you could just run this simple data step.
data want ;
set have ;
output;
do year=year+1 to year+5 ;
pia = pia*(1+cola);
output;
end;
run;
result
Obs year pia cola 1 2018 1000.00 0.02 2 2019 1020.00 0.02 3 2020 1040.40 0.02 4 2021 1061.21 0.02 5 2022 1082.43 0.02 6 2023 1104.08 0.02
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.