Look-Ahead and Look-Back

[JeffM]

Hi,

I have a data set of product sales and pricing that is sorted by product by week. I want to create a data step that "looks back" 12 weeks from the current week and selects the maximum price for that product. The 12 week "look back" period would then move forward as the data step progresses.

Is this possible?

Also, i'm NOT a sas coder. Simple data steps are my speed.

Jeff

[Reeza]

Do you have a license for SAS ETS? If so, PROC EXPAND can do this quite easily. You can check what products you have by using the following code and checking the log:

proc product_status;run;

[JeffM]

I use SAS-EG and I am not familiar with SAS language. I want to create a process in EG and plug a data step in the middle.

[Reeza]

There's a set of time series procedures in EG under tasks, did you try there first? If you have SAS/ETS these tasks will do what you need.

[JeffM]

I actually AM programming....just a data step.  I am not writing the whole thing in code.  The rest of the process will be all queries.  I have extensive experience with VBA so I understand what's going on.  I just don't know the SAS language.

[Reeza]

And I'm going to move this post to the EG section, since you're not programming.

[Patrick]

"Also, i'm NOT a sas coder. Simple data steps are my speed."

EG point&click doesn't create a lot of SAS data step code so I'd say using SAS data step in a code window is coding.

Having said that: If you can't use the ETS tasks then you still could use the query builder.

With the query builder:

1. left join your source table with itself

2. select all variables from the left table

3. create a computed column using avg_price from your right table with a formula like: max(t2.avg_price)

4. Tick box "select distinct rows only"

5. the join condition needs to be something like:

     t1.item=t2.item AND t2.week between t1.week-12 and t1.week   
    ... or depending how you define the look-back period: t2.week between t1.week-13 and t1.week-1 

[JeffM]

You are correct.  I am creating a process in EG with queries BUT I want to plug a data step in the middle to do this one thing.  The issue with your reply is that the source data file is large so the joins will grind it to a halt.  I also need the 12 week window to move...like the Price Is Right guess the price game if you've seen it.

Thanks for the reply

[Patrick]

@JeffM 

What does "large" mean for you? Tell us more about your data.

The SQL I've proposed will do a rolling sum over a 12 week window.

The required data step logic for a rolling sum is most likely no more beginner level. Coding for performance normally adds additional complexity.

You tell us that you are NO coder which at least to me sends the clear message that you're going to reject any data step code above beginner level.

[Ksharp]

Here could give you some hint ,if you know SQL somewhat .

proc sql;
create table want as
 select *,(select max(air) from sashelp.air
where date between intnx('week',a.date,-12,'s') and a.date ) as max_12w_back 
  from sashelp.air as a;
quit

[JeffM]

Thanks, but this thread got moved to the wrong spot.  Imagine a spreadsheet with 1B records.  I need to copy a formula down that calculates a rolling 12 week max.  I am looking for tips in a data step on how to do this.

[Reeza]

Not the wrong spot, you're using EG and there are several methods in EG to do this without using a data step, via a task or query builder.

Here are several variations are here on how to do this and you've received several options above. If one doesn't work, please post back and explain how it's not working.

https://blogs.sas.com/content/iml/2016/01/27/moving-average-in-sas.html