turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Use prior calculated value for current value

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-17-2017 06:08 PM - edited 08-17-2017 06:25 PM

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!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to su17

08-17-2017 06:17 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

08-17-2017 06:27 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

08-17-2017 06:33 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to su17

08-17-2017 07:34 PM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to su17

08-17-2017 08:01 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

08-17-2017 08:17 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to su17

08-17-2017 09:07 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to su17

08-18-2017 11:01 AM

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.