How do I create a SAS variable that depends on itself?

Question

I have a dataset (see picture below). I need to fill the stitched_price column. For row 1, I need to use the value from 'price.' This works.

For row 2 onwards, I need to do the following: stitched_price = lag(stitched_price) * (trade_return + 1). However, this doesn't work and leaves all rows > 1 blank.

Here is my code:

data test2;
    set test;
    if _N_ = 1 then stitched_price = price;
    else stitched_price = lag(stitched_price) * (1 + trade_return);
run;

I am not sure why this is happening. I understand that there are intricacies involved with using lag in an if statement, but is there a way around this?

This is the result of the code. Prior to running the code, everything is the same except for the absence of the stitched_price variable.

possible duplicate of [lag function doesn't work in SAS](http://stackoverflow.com/questions/9484728/lag-function-doesnt-work-in-sas) — Robert Penridge, Dec 26 '14 at 15:38
@RobertPenridge Not true. I read that question, which lead me to believe that the above code will work. It takes substantially more effort to type and take screenshots of my work than simply searching for it. I asked only after searching over several venues. — navkast, Dec 27 '14 at 14:04
The answers for the linked question discuss both how to use `lag()` correctly as well as mention using the `retain` statement as an alternative. — Robert Penridge, Dec 29 '14 at 15:28

score 2 · Accepted Answer · edited May 23 '17 at 10:26

2

Try retain instead:

data test2;
   set test;
    retain stitched_price;
    if _N_ = 1 then stitched_price = price;
    else stitched_price = stitched_price * (1 + trade_return);
run;

In general lag doesn't work in conditional blocks. If you did want to use lag, it would be something like follows:

data test2;
   set test;
    lag_s=lag(stitched_price);
    if _N_ = 1 then stitched_price = price;
    else stitched_price = lag_s * (1 + trade_return);
run;

Here's an older post on why lag doesn't work and I'm sure Google has many others: lag function doesn't work in SAS

edited May 23 '17 at 10:26

Community

1
1

answered Dec 23 '14 at 22:58

Reeza

20,510
4
21
38

Your retain answer works! I completely forgot about the retain statement. Oops. – navkast Dec 24 '14 at 21:40

How do I create a SAS variable that depends on itself?

1 Answers1