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
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 ;
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.
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
A little clarification:
The calculation above in Excel is correct.
How can we do it in SAS ?
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 ;
Many Thanks !
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.