Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- PROC EXPAND moving average from previous 7 calendar days

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 02-03-2021 12:10 PM
(2126 views)

I'm trying to do a moving average with PROC EXPAND where the average takes into account only those observations that occur within the previous seven calendar days, which is not necessarily the previous seven observations. I have a dataset that has some gaps, such as weekends, holidays, etc., where the data is not reported. But I don't want to average observations that are outside the weekly window. In other words, if I'm calculating the moving average on a Monday, I want the average to be only those observations from the previous Tuesday to the current day, regardless of whether there are observations for the weekend.

Currently PROC EXPAND is taking seven previous observations, which can average in numbers from a few days before that. Here's my code:

```
proc expand DATA=gas.gasprices OUT=gas.GasPrices_ma METHOD=none;
ID Date;
convert spread=Spread_MA / TRANSFORMOUT = (nomiss movave 7 trim 6);
run;
```

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Use PROC TIMESERIES to first fill in the gaps with missing and then use PROC EXPAND to do the moving averages.

You could also use a data step if so inclined, but this method is easier as it will scale to month/years/weeks as necessary.

Here's a fully worked example that shows how this is done at the monthly level, you will need to change the INTERVAL to day for your case.

/*this is an example of time series data. 1. Create a time series data set with missing intervals (IBM) 2. Add back missing entries using PROC TIMESERIES (IBM_NO_MISSING) 3. Calculate moving average - 12 month average *//*1*/data ibm; set sashelp.stocks; where stock='IBM'; if month(date)=7 then delete; run; proc sort data=ibm; by date; run;/*2*/proc timeseries data=ibm out=ibm_no_missing; id date interval=month start='01Aug1986'd end='01Dec2005'd; var open; run;/*3*/proc expand data=ibm_no_missing out=want; id date; convert open = open_12 / method=none transformout= (movave 12); run;

@ebowen wrote:

I'm trying to do a moving average with PROC EXPAND where the average takes into account only those observations that occur within the previous seven calendar days, which is not necessarily the previous seven observations. I have a dataset that has some gaps, such as weekends, holidays, etc., where the data is not reported. But I don't want to average observations that are outside the weekly window. In other words, if I'm calculating the moving average on a Monday, I want the average to be only those observations from the previous Tuesday to the current day, regardless of whether there are observations for the weekend.

Currently PROC EXPAND is taking seven previous observations, which can average in numbers from a few days before that. Here's my code, data attached:

`proc expand DATA=gas.gasprices OUT=gas.GasPrices_ma METHOD=none; ID Date; convert spread=Spread_MA / TRANSFORMOUT = (nomiss movave 7 trim 6); run;`

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Use PROC TIMESERIES to first fill in the gaps with missing and then use PROC EXPAND to do the moving averages.

You could also use a data step if so inclined, but this method is easier as it will scale to month/years/weeks as necessary.

Here's a fully worked example that shows how this is done at the monthly level, you will need to change the INTERVAL to day for your case.

/*this is an example of time series data. 1. Create a time series data set with missing intervals (IBM) 2. Add back missing entries using PROC TIMESERIES (IBM_NO_MISSING) 3. Calculate moving average - 12 month average *//*1*/data ibm; set sashelp.stocks; where stock='IBM'; if month(date)=7 then delete; run; proc sort data=ibm; by date; run;/*2*/proc timeseries data=ibm out=ibm_no_missing; id date interval=month start='01Aug1986'd end='01Dec2005'd; var open; run;/*3*/proc expand data=ibm_no_missing out=want; id date; convert open = open_12 / method=none transformout= (movave 12); run;

@ebowen wrote:

I'm trying to do a moving average with PROC EXPAND where the average takes into account only those observations that occur within the previous seven calendar days, which is not necessarily the previous seven observations. I have a dataset that has some gaps, such as weekends, holidays, etc., where the data is not reported. But I don't want to average observations that are outside the weekly window. In other words, if I'm calculating the moving average on a Monday, I want the average to be only those observations from the previous Tuesday to the current day, regardless of whether there are observations for the weekend.

Currently PROC EXPAND is taking seven previous observations, which can average in numbers from a few days before that. Here's my code, data attached:

`proc expand DATA=gas.gasprices OUT=gas.GasPrices_ma METHOD=none; ID Date; convert spread=Spread_MA / TRANSFORMOUT = (nomiss movave 7 trim 6); run;`

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hello All

Can anyone tell me how to do 7 hourly moving average timeseries data?

Using proc expand..

Can anyone tell me how to do 7 hourly moving average timeseries data?

Using proc expand..

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Is your data already hourly?

If so something as simple as this may work:

proc expand data=have out=want method=none;

id datetimevariable;

convert value = value7 / transout = (moveave 7);

run;

https://support.sas.com/documentation/cdl/en/etsug/68148/HTML/default/viewer.htm#etsug_expand_exampl...

If so something as simple as this may work:

proc expand data=have out=want method=none;

id datetimevariable;

convert value = value7 / transout = (moveave 7);

run;

https://support.sas.com/documentation/cdl/en/etsug/68148/HTML/default/viewer.htm#etsug_expand_exampl...

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I've a same datetime 30 observation with sequence 1 and datetime format date variable and sequence variable which is group by sequence. I want to do 7 hourly moving average timeseries data and plot by sequence.

And I've missing data also in value.

And I've missing data also in value.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Please start a new thread with your question and include sample data that reflects your actual data, fake data is fine.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I just add to=hour and sorting group by variable then it's work very well.

Thank you for your help and support.

😀😇🤩

Thank you for your help and support.

😀😇🤩

**Don't miss out on SAS Innovate - Register now for the FREE Livestream!**

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.