BookmarkSubscribeRSS Feed
su17
Calcite | Level 5

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!

8 REPLIES 8
Reeza
Super User

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. 

su17
Calcite | Level 5
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.
su17
Calcite | Level 5

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.

su17
Calcite | Level 5

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;

Reeza
Super User

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.

su17
Calcite | Level 5
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.
Reeza
Super User

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;
ballardw
Super User

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.

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
  • 8 replies
  • 1087 views
  • 0 likes
  • 3 in conversation