BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
arunmmw
Fluorite | Level 6

Hi,

 

 I am trying to create moving averages by looking at lag - lag 1 and lag2 values of rewards redemption. Here is the formula current = (lag - lag2) / (lag2 - lag3).

 

However, the current value is not stored sequencially in the rows on update. Meaning,

 

when i find row4 current value is missing, i am using this formula to come up with row4 rewards. Now, row4 -> current = 100

 

When moving to row5 - new rewards calculated in row4 isnt available . lag1 -> row4> current = 0 (i guess lag values are not dynamic)

 

 

 

DATA vtest;
SET v10;
by v_id group id;

current  = red1;


lag_red1 = lag(newredemption1);
lag2_red1 = lag2(newredemption);
lag3_red1 = lag3(newredemption);


expected_increase_pts = (lag_red1*(lag_red1 / lag2_red1)) - lag2_red1;
previos_redeem_growth = (lag_red1 - lag2_red1) / (lag2_red1 - lag3_red1);

current  = current  + (expected_increase_pts * previos_redeem_growth);

retain current ;

 

RUN;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

It would really help to understand what you are attempting if you provide some data in the form of a datastep for input and what you expect for the output for that example data.

 

 

I do wonder, since you say "current" is sometimes missing that you want

current  = sum(current , (expected_increase_pts * previos_redeem_growth));

 

Something to consider: you need to address all of the potential missing values before doing division with the lagged values.

Lag3 will not exist until you get to the 4th observation. So you are going to have division by missing which you may want to consider.

And if your newredemption variable is evermissing you're going to get intermittent other calculations with missing values.

 

Lag values are only for variables read in through a SET or MERGE statement. If you want to keep a calculated variable then you use RETAIN. When to reset to initial values or missing for retained variables is sometimes interesting. You might want something like:

 

Retain TempCurrent 0; /* this set an intial value of 0*/

 

<misc calculations>

if missing(current) then current=sum(TempCurrent, (expected_increase_pts * previos_redeem_growth));

else current =  sum(current , (expected_increase_pts * previos_redeem_growth));

/* and then reset the retained value*/

TempCurrent = current;

View solution in original post

3 REPLIES 3
ballardw
Super User

It would really help to understand what you are attempting if you provide some data in the form of a datastep for input and what you expect for the output for that example data.

 

 

I do wonder, since you say "current" is sometimes missing that you want

current  = sum(current , (expected_increase_pts * previos_redeem_growth));

 

Something to consider: you need to address all of the potential missing values before doing division with the lagged values.

Lag3 will not exist until you get to the 4th observation. So you are going to have division by missing which you may want to consider.

And if your newredemption variable is evermissing you're going to get intermittent other calculations with missing values.

 

Lag values are only for variables read in through a SET or MERGE statement. If you want to keep a calculated variable then you use RETAIN. When to reset to initial values or missing for retained variables is sometimes interesting. You might want something like:

 

Retain TempCurrent 0; /* this set an intial value of 0*/

 

<misc calculations>

if missing(current) then current=sum(TempCurrent, (expected_increase_pts * previos_redeem_growth));

else current =  sum(current , (expected_increase_pts * previos_redeem_growth));

/* and then reset the retained value*/

TempCurrent = current;

Ksharp
Super User

Yes. LAG() is not dynamic. Use ARRAY instead .

Post some real data to explain your question well.

arunmmw
Fluorite | Level 6

Thank you 

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