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
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.
"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
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
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.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: