topic Re: Look-Ahead and Look-Back in SAS Enterprise Guide

Look-Ahead and Look-Back

JeffM — Wed, 05 Jun 2019 21:02:44 GMT

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

Re: Look-Ahead and Look-Back

Reeza — Wed, 05 Jun 2019 21:13:11 GMT

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;

Re: Look-Ahead and Look-Back

JeffM — Wed, 05 Jun 2019 21:15:18 GMT

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.

Re: Look-Ahead and Look-Back

Reeza — Wed, 05 Jun 2019 21:16:16 GMT

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.

Re: Look-Ahead and Look-Back

Reeza — Wed, 05 Jun 2019 21:16:41 GMT

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

Re: Look-Ahead and Look-Back

Patrick — Thu, 06 Jun 2019 03:49:57 GMT

"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

Re: Look-Ahead and Look-Back

JeffM — Thu, 06 Jun 2019 12:24:41 GMT

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.

Re: Look-Ahead and Look-Back

JeffM — Thu, 06 Jun 2019 12:27:02 GMT

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

Re: Look-Ahead and Look-Back

Ksharp — Thu, 06 Jun 2019 13:40:16 GMT

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

Re: Look-Ahead and Look-Back

JeffM — Thu, 06 Jun 2019 13:47:36 GMT

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.

Re: Look-Ahead and Look-Back

Reeza — Thu, 06 Jun 2019 14:56:17 GMT

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

Re: Look-Ahead and Look-Back

Patrick — Thu, 06 Jun 2019 21:43:01 GMT

@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.