How do I get each observation of a variable to move up by a percent of itself in the previous observ

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

How do I get each observation of a variable to move up by a percent of itself in the previous observ

[ Edited ]

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:

 

YEARPIACOLAADJ_PIA
2018 $1,000.000 $1,000.00
2019 $1,000.002% $1,020.00
2020 $1,000.002% $1,040.40
2021 $1,000.002% $1,061.21
2022 $1,000.002% $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);

 

Capture.PNG

 

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.

 

Capture2.PNG

 

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.

 


Accepted Solutions
Solution
a week ago
Super User
Super User
Posts: 8,272

Re: How do I get each observation of a variable to move up by a percent of itself in the previous ob

[ Edited ]
Posted in reply to kharrigan

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

 

View solution in original post


All Replies
Super User
Super User
Posts: 8,272

Re: How do I get each observation of a variable to move up by a percent of itself in the previous ob

Posted in reply to kharrigan

The statement you have after ELSE is not a valid SAS statement.  Don't you want an assignment statement there?

Occasional Contributor
Posts: 5

Re: How do I get each observation of a variable to move up by a percent of itself in the previous ob

Don't you want an assignment statement there?

I have not worked with assignment statements.  I will research and try this out.

Super User
Super User
Posts: 8,272

Re: How do I get each observation of a variable to move up by a percent of itself in the previous ob

Posted in reply to kharrigan

@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.

Occasional Contributor
Posts: 5

Re: How do I get each observation of a variable to move up by a percent of itself in the previous ob

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'
Super User
Super User
Posts: 8,272

Re: How do I get each observation of a variable to move up by a percent of itself in the previous ob

Posted in reply to kharrigan

@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.

Occasional Contributor
Posts: 5

Re: How do I get each observation of a variable to move up by a percent of itself in the previous ob

I apologize.  I didn't realize I could just copy and paste from Excel into the text box.  I've updated my post.

Super User
Posts: 2,504

Re: How do I get each observation of a variable to move up by a percent of itself in the previous ob

Posted in reply to kharrigan

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);

 

Occasional Contributor
Posts: 5

Re: How do I get each observation of a variable to move up by a percent of itself in the previous ob

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:

Capture2.PNG

 

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.

Solution
a week ago
Super User
Super User
Posts: 8,272

Re: How do I get each observation of a variable to move up by a percent of itself in the previous ob

[ Edited ]
Posted in reply to kharrigan

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 81 views
  • 0 likes
  • 3 in conversation