BookmarkSubscribeRSS Feed
udaypatel1
Fluorite | Level 6

Hello,

 

I am attempting to create a moving average of a time frame of certain days in a week and for 1 week either side of my day of interrogation.

For example, I have a measure for this wednesday. I want to take an average of the wednesday before, this wednesday and next wednesday ONLY if it meets a certain criteria.

I have data for all weekdays which also need to be averaged.

Is the best way forward to do an IF ELSE statement to filter for certain weekdays then embed some sort of average calculation in the same statement?

 

My table has the dates and the measures are such and I need to calculate the average like so:

Date                      Measure      Average

Monday 1st           10               13.3

Tuesday 2nd         15               13.3

Wednesday 3rd    10                11.6

Thursday 4th        20                20

Friday 5th             10                18.3

Saturday 6th         15                11.6 

Sunday 7th           10                13.3

Monday 8th          20                13.3

Tuesday 9th         15                13.3

Wednesday 10th  10                11.6

Thursday 11th      20                20

Friday 12th           20                18.3

Saturday 13th      10                 11.6

Sunday 14th        15                 13.3

Monday 15th        10                13.3

Tuesday 16th       10                13.3

Wednesday 17th  15                11.6

Thursday 18th      20                 20

Friday 19th           25                 18.3

Saturday 20th       10                 11.6

Sunday 21st          15                 13.3

 

I am used to the query builder or I can write out the code, I just need some guidance please on the method.

 

10 REPLIES 10
Reeza
Super User

You have too many vague questions at once. Try explaining one of the calculations first. 

udaypatel1
Fluorite | Level 6

Hi, thanks for your response.

 

Imagine I have 3 weeks of data, Mon-Sun.

I want to average individual days. Mondays, Tuesdays, Wednesdays etc.

Now I also want to be able to selectively exclude certain weekdays if they do not meet a certain criteria. I will focus on averaging all the days first though, then work on the exclusion criteria.

 

Should I split the weekdays into individual data sets, order them, apply a lag function and average it?

Or is there another quicker method to do this?

 

 

Reeza
Super User

Use a query builder or summary task. 

 

In query builder add all columns to select list, create two computed columns, one that calculates the weekday (weekday function) and one that calculates average of the variable you want. Use the calculated weekday column in the GROUP BY section. 

 

Reeza
Super User

PS by definition this is not a moving average. 

udaypatel1
Fluorite | Level 6

Hi Reeza,

 

Thank you for your answer. The moving average is still required as I need to be able to select just 3 weeks at a time (current week and weeks either side).

I have years of dates which I need to perform this on. I can do the average pretty easily as you had suggested, it was more a case of selecting only a handful of neighbouring weeks.

If you could think of anything that could help Im willing to try it.

I am attempting to use the example in this paper: http://www2.sas.com/proceedings/forum2008/093-2008.pdf

 

Thanks!

Reeza
Super User

Look at proc expand then. 

ChrisHemedinger
Community Manager

There's a great blog post by @Rick_SAS: How to compute a moving average with SAS.

 

If you want to include just certain days, or classify by day of the week -- then you might need to calc some additional classifier variables based on the date field that you've included.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
udaypatel1
Fluorite | Level 6

Hi,

 

Thanks for that. I have tried to apply the same using SAS EG but I do not think it is entirely possible.

I think the proc does not work in EG. Is there a workaround for this proc in SAS EG?

 

Kind Regards,

Reeza
Super User

You need a SAS/ETS license for PROC EXPAND and it is available in EG, if you have the license. 

 

If you don't then try the temporary array method:

 

https://gist.github.com/statgeek/27e23c015eae7953eff2

udaypatel1
Fluorite | Level 6

Hi Reeza,

 

thank you for the suggestion.

 

I have attempted to use the script as posted:

 

data want;
array p{0:3} _temporary_;
set have; by object;
if first.object then call missing(of p{*});
p{mod(_n_,4)} = price;
lowest = min(of p{*});
highest = max(of p{*});
run;

 

In the 3rd line, would I be replacing 'object' with a column in my data set which is numerically ordered such as a date?

 

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!

SAS Enterprise Guide vs. SAS Studio

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.

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
  • 10 replies
  • 1523 views
  • 4 likes
  • 3 in conversation