topic Re: How to calculate a variable based on a lag logic in SAS Programming

How to calculate a variable based on a lag logic

J111 — Thu, 21 Sep 2023 09:51:17 GMT

Hello,

Please find below data Have and data Want.

The index for the first observation is 1 and then it is based on the following logic

index = lag(index) + rate/100/365 ;

-------------------------------------
data Have ;
input obs rate ;
cards ;
1 0.35
2 0.35
3 0.35
4 0.35
;
run ;

----------------------------

data Want ;
input obs index ;
cards ;
1 1
2 1.00000959
3 1.00001918
4 1.00002877
;
run ;

-----------------------------

My following code test was not good, pls adivse

data next ;
set have ;
length Index 8 ;
retain index 1 ;
_value = lag(index) ;
if obs gt 1 then index = _value + rate/100/365 ;
run ;

--------------------

This is originally from EXCEL function ( E2+C3/100/365 ) such that column E is the index and column C is the rate.

Thanks

Amir — Thu, 21 Sep 2023 10:30:13 GMT

Hi,

Perhaps I have not understood what you're asking, but when I ran your code, the index column that you calculated appears to match your want data:

Were you looking for something else?

Thanks & kind regards,

Amir.

J111 — Thu, 21 Sep 2023 10:32:37 GMT

Hi,

Each observation should get a different value of index.

However, in the test code both observation 2 and 3 get the same value of index.

This is wrong.

Thanks

J111 — Thu, 21 Sep 2023 11:21:16 GMT

A little clarification:

The calculation above in Excel is correct.

How can we do it in SAS ?

mkeintz — Thu, 21 Sep 2023 13:16:11 GMT

The problem is related to the fact that LAG is not a lookback function - it is an update FIFO queue function. And you are updating that queue in the

 _value = lag(index) ;

statement prior to the recalculation of INDEX in the

if obs  gt 1  then index = _value + rate/100/365 ;

statement. So the recalculated INDEX value does not get into the queue in a timely way.

But in this case, you don't need LAG at all, since you are retaining INDEX.

Simplify your code to

data next ;
  set have ;
  length Index 8 ;
  retain index 1 ;
  if obs gt 1 then index = index + rate/100/365;
run ;

J111 — Thu, 21 Sep 2023 11:59:38 GMT

Many Thanks !