BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kharrigan
Calcite | Level 5

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

9 REPLIES 9
Tom
Super User Tom
Super User

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

kharrigan
Calcite | Level 5
Don't you want an assignment statement there?

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

Tom
Super User Tom
Super User

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

kharrigan
Calcite | Level 5

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'
Tom
Super User Tom
Super User

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

kharrigan
Calcite | Level 5

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

ChrisNZ
Tourmaline | Level 20

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

 

kharrigan
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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