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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.