Use prior calculated value for current value

Reply
Occasional Contributor
Posts: 11

Use prior calculated value for current value

[ Edited ]

Hi,

 

Hoping someone can help with this coding problem:

 

I need to calculate the current value of a variables based on prior values of one of two variables depending on observation. For example,

 

Z(t) = X(t-k)*Y(t) for early t

Z(t) = Z(t-k)*Y(t) for later t

 

X and Y are given but Z is always calculated, initially from prior X and current Y but eventually from prior Z and current Y. I have been able to do this with arrays but not in a data step using retain, lag, and output statements.

 

An Excel version of the logic is provided.

 

I am using PC SAS 9.4.

 

Thanks for the help!

Super User
Posts: 24,010

Re: Use prior calculated value for current value

There's no differences in those formulas. You need to explain this more, there may be a better way in SAS and you don't explain the logic at all. Where does 6 in the formula come from?

 

Just based on your description, which doesn't align well with your workbook, Look at the LAG or RETAIN functions. 

Occasional Contributor
Posts: 11

Re: Use prior calculated value for current value

Thx. 2nd formula should have Z(t-k) so uses prior calculated values for Z. I have tried retain, lag, but cannot replicate the Excel values.
Occasional Contributor
Posts: 11

Re: Use prior calculated value for current value

The 6 in the Excel example is where the formula switches. The calculation uses a lag of 3 observations for beyond observation 6 I want to base current Z on prior Z calculations.

Occasional Contributor
Posts: 11

Re: Use prior calculated value for current value

Here is some code I have tried to go with the new Excel example:

 


/*

Input dataset:

t = observation
X and Y are given
Zxl is the value calculated in Excel that we must match

This example uses a 3-observation lag:

For t<=6: Z(t) = X(t-3)*exp(Y)
For t>6: Z(t) = Z(t-3)*exp(Y)

*/

proc import datafile="C:\Users\brownrs\Desktop\logic example.xlsx" out=indata replace dbms=xlsx;
 getnames=yes;
  range="indata";
run;

proc print data=indata;
run;

data test;
  set indata;

  retain Z;
  if t<=6 then Z=lag3(X)*exp(Y);
/*
 else if t>6 then Z=lag3(Z)*exp(Y);
*/
  output;
 Z=lag2(Z)*exp((Y);
/*
 output;
  Z=lag3(Z)*exp(Y);
*/

run;

proc print data=test;
  var h t Zxl Z;
run;

Super User
Posts: 24,010

Re: Use prior calculated value for current value

Ok, so its not clear what you start with. What exactly do you start with? It looks like  you're creating additional rows - so don't include those.

Occasional Contributor
Posts: 11

Re: Use prior calculated value for current value

X and Y are given, and the lag value of 3, so I want to calculate Z in SAS. Zxl is the value from the Excel example for validation of the SAS logic.
Super User
Posts: 24,010

Re: Use prior calculated value for current value

I'm blanking a bit tonight and spent too much time on this already. 

It should be easy, but my brains not working, here's what I have so far. It does the first three correctly but not the remaining. Note that your data should be imported already, I've assumed its called HAVE. You'll also have small differences due to rounding. You shouldn't have that issue with your actual data that you've imported, only if you use the sample data below. 

 

data have;
	infile cards dlm='09'x;
	input x y;
	cards;
179.12845	0.24603
196.30272	0.27820
214.99623	0.31072
231.15299	0.29565
249.33082	0.30774
282.70958	0.23816
312.98346	0.27196
341.00267	0.24553
371.52599	0.36183
409.24344	0.37525
453.35709	0.19067
505.46674	0.29266
552.48362	0.35049
607.98211	0.33740
685.19173	0.28681
;
run;

*create data lags;

data lagged;
	set have;
	retain Z Z1 Z2 Z3;

	x3=lag3(x);
	z3=lag3(z);

	if _n_ > 6 then
		Z=Z3*exp(y);
	else
		Z=x3*exp(y);
		
Z3=Z2;
Z2=Z1;
Z1=Z;


run;
Super User
Posts: 13,941

Re: Use prior calculated value for current value

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Ask a Question
Discussion stats
  • 8 replies
  • 208 views
  • 0 likes
  • 3 in conversation