Iterative Loop

[sas_user_1001]

I'm trying to get a sample of data I have to grab a lagged value and pull it forward and have it by 5% larger than the last value . For instance:

data have;

input year month  x_var;

datalines;

2020 1 5

2020 2 10

2020 3 15

2020 4 25

What I would like to end up with is the following output:

2021 1 5.25     *Note: 5*1.05;

2021 2 10.5     *Note: 10*1.05;

2021 3 15.75   *Note: 15*1.05;

2021 4 26.25   *Note:25*1.05;

2022 1 5.5125     *Note: 5.25*1.05;

2022 2 11.025     *Note: 10.5*1.05;

2022 3 16.5375   *Note: 15.75*1.05;

2022 4 27.5625   *Note: 26.25*1.05;

Ideally, I want to be able to carry forward for more years, but I'm trying to get this to work for a small sample of data first. I tried the following, but it isn't producing the desired result.

data want;

set have;

do year=2021 to 2022;

do month=1 to 4;

x_var = lag4(x_var);

output;

end;

end;

run;

[Tom]

It does not look like you are doing anything that needs lags.  Instead if looks like you are converting one observation into 3.  This code gets the data you show.

data have;
  input year month  x_var;
datalines;
2020 1 5
2020 2 10
2020 3 15
2020 4 25
;

data want;
  set have;
  new=x_var;
  do year=year to year+2 ;
    output;
    new=new*(1+x_var/100);
  end;
run;

You can add a PROC SORT to change the observation order if you want.

But your calculations seem strange.  Normally you would have a separate variables for the current amount and the percent increase.   But you seem to be using X_VAR as both the percentage to increase and the starting value. 

[sas_user_1001]

Regarding the data, I am trying to calculate the year-over-year forecast for each monthly data point. Thus, in the full dataset, I can capture some seasonality features. That is, the value in January 2020 will be quite different than the value in August 2020, so I can't iterate growth month-over-month.

[ballardw · Accepted solution]

The output from that failed attempt should have shown you that the Lag inside a loop is reusing the same value.

This code "works" for this example:

data have;
input year month  x_var;
datalines;
2020 1 5
2020 2 10
2020 3 15
2020 4 25
;


data example ;
  set have;
  do year=year to (year+1);
     output;
     x_var= x_var* 1.05;
  end;
  output;
run;

proc sort data=example;
   by year month;
run;

Incrementing by year and then sorting is going be much more efficient than trying "lag" to anything.

Note with the first output at the top of the iterated Do /end block writes the original values. Then calculates the next value which gets written. The second output is the write the last x_var.

Then SORT to be the order you want.

The trick is if you are going to change the incrementing value at different years.

With your original question with all those ugly variables then you would place them into an array and then iterate over the array where the X_var single assignment is.

[sas_user_1001]

Thanks -- yes, I plan to change the growth rate for year further down the line, so will need to figure out how to implement that. For instance, I may have the values grow at 5% for the first couple years, but then change it to 3% the next to years, then 1%, and so on...

[ballardw]

---

@sas_user_1001 wrote:

Thanks -- yes, I plan to change the growth rate for year further down the line, so will need to figure out how to implement that. For instance, I may have the values grow at 5% for the first couple years, but then change it to 3% the next to years, then 1%, and so on...

---

You could place the rates into a temporary array defined to use the YEAR as the index IF every observation in that year is going to use the same rate. Example:

data _null_;
   array rate(2010:2015) _temporary_ (1.05, 1.05, 1.1, 1.1, 1.15, 1.2);
   file print;
   do year=2010 to 2015;
      r = rate[year];
      put 'The value of rate for ' year +1 'is ' r;
   end;
run;

The (2010:2015) in the array definition mean that the lower bound to address members of the array is 2010 and the upper is 2015. _temporary_ means that the created variables will not be written to the output data set, if any. The comma delimited list of values following _temporary_ assigns values to the elements of the array. If you use an assignment list you have to provide a value for each element which could be . for missing.

So any place we used 1.05  (or 0.05) the equivalent value could be selected using rate[year] , or other numeric variable with integer values of 2010 to 2015.

[sas_user_1001]

This is great thank you! -- as a follow up, if I have the growth rates in a table in SAS, is there a way to store them as variable names and utilize those variable names in the list? I only ask because a few of the rates are carried out to 12 digits for precision. Thanks again.

[AhmedAl_Attar]

Hi @sas_user_1001 

While I see you have already accepted a solution, here is an approach that gives the ability to have variable growth rates for future years. Yet another implementation that utilizes two dimensional array.

data have;
	input year month x_var;
	datalines;
2020 1 5
2020 2 10
2020 3 15
2020 4 25
;
run;

data want(KEEP= year month x_var gr_pct);
	if (0) then SET have;
	array x{20:24,1:4} 8 _temporary_; /* Change these dimensions to fit your needs */
	
	array gr{5} 3 gr1-gr5 (0, 0.05, 0.05, 0.03, 0.01); /* Annual growth rate */
	
	/* Load the 2020 (have) data values */
	do _n_=1 by 1 until(eof);
		set have end=eof;
		x{20,_n_} = x_var;
	end;
	
	/* ---------------------------- */
	/* Process future forecast data */
	/* ---------------------------- */
	gi = 1; /* growth rate index */
	
	do y=21 to hbound1(X);
		gi+1;
		do m=lbound2(X) to hbound2(X);
			x{y,m} = x{(y-1),m} * (1 + gr{gi});
			put x{y,m} =;
		end;
	end;
	
	/* ------------------------ */
	/* Generate desired output */
	/* ------------------------ */
	gi = 0;
	do y=lbound1(X) to hbound1(X);
		gi+1;
		do m=lbound2(X) to hbound2(X);
			year = 2000+y;
			month = m;
			x_var = x{y,m};
			gr_pct = gr{gi};
			output;
		end;
	end;
	stop;
run;

Hope this helps,

Ahmed