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
- /
- Base SAS Programming
- /
- LAG for missing values - moving average

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-18-2016 05:35 PM

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;

Accepted Solutions

Solution

03-24-2016
09:31 AM

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

Posted in reply to arunmmw

03-18-2016 06:41 PM

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;

All Replies

Solution

03-24-2016
09:31 AM

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

Posted in reply to arunmmw

03-18-2016 06:41 PM

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;

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

Posted in reply to arunmmw

03-19-2016 06:48 AM - edited 03-19-2016 06:50 AM

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

Post some real data to explain your question well.

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

Posted in reply to arunmmw

03-24-2016 09:32 AM

Thank you ballardw and ksharp. Appreciate you both helping out