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

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

kharrigan — Tue, 07 Aug 2018 15:10:43 GMT

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.

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

Tom — Tue, 07 Aug 2018 04:39:03 GMT

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

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

Tom — Tue, 07 Aug 2018 04:42:40 GMT

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

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

ChrisNZ — Tue, 07 Aug 2018 04:43:34 GMT

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

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

kharrigan — Tue, 07 Aug 2018 15:06:26 GMT

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.

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

kharrigan — Tue, 07 Aug 2018 15:11:52 GMT

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

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

kharrigan — Tue, 07 Aug 2018 15:13:35 GMT

Don't you want an assignment statement there?

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

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

Tom — Tue, 07 Aug 2018 15:15:47 GMT

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

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

Tom — Tue, 07 Aug 2018 15:22:48 GMT

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

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

kharrigan — Tue, 07 Aug 2018 15:31:44 GMT

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'