Solved
Contributor
Posts: 41

# Moving average & proc expand - Quick question

Quick question on creating moving averages.  I believe proc expand is what I want, but I can't get it to perform the exact operation I want.

On the overview level, I need to create moving averages for the last year based upon time series data for different individuals.  The problem is that not every date is represented as an observation.  The problem rears its head when I run the following code:

proc expand data=test out=outtest;

by ID;

id date;

convert amount= rollave / method=none transformout = (movave 365);

run;

The results of the above code give me averages using the 365 observations above the current one (assuming same ID) and NOT the observations for the 365 days prior to the observation day.

As a simple example, lets assume I want a moving average of the last week and use the following code with the following data (very similar to my ultimate problem, but averaging only a week instead of a year).

CODE:

proc expand data=test out=outtest;

by ID;

id date;

convert amount= rollave / method=none transformout = (movave 7);

run;

DATA:

IDDateAmount
1

1/1/1990

2
11/2/19904
11/4/19903
11/5/19905
11/7/19905
11/9/199010
11/10/19905
11/14/199010

The average I want for 1/14/1990 should be based upon the amounts from the 3 observations with dates of 1/14/1990, 1/10/1990, and 1/9/1990 (the week ending 1/14/1990 spans 1/08/1990 to 1/14/1990) .  However, the results I get are based upon the 7 observations ranging from 1/2/1990 to 1/14/1990.

Is there an easy change in proc expand to get the behavior I want?  If not, any suggestions for how to quickly achieve my goal?

Accepted Solutions
Solution
‎05-14-2012 03:17 PM
Posts: 3,167

## Re: Moving average & proc expand - Quick question

Hi,

I don't think proc expand can meet what you need alone. With the help from proc timeseries, you may get what you want:

data have;

input ID Date :mmddyy10. Amount;

cards;

1 1/1/1990 2

1 1/2/1990 4

1 1/4/1990 3

1 1/5/1990 5

1 1/7/1990 5

1 1/9/1990 10

1 1/10/1990 5

1 1/14/1990 10

;

/*to fillup the blanks*/

proc timeseries data=have out=want;

id date interval=day ;

var amount;

by id;

run;

/*to calculate the moving average*/

proc expand data=want out=outtest;

by ID;

id date;

convert amount= rollave / method=none transformout = (movave 7);

run;

You can always remove the missing value later.

Regards,

Haikuo

All Replies
Solution
‎05-14-2012 03:17 PM
Posts: 3,167

## Re: Moving average & proc expand - Quick question

Hi,

I don't think proc expand can meet what you need alone. With the help from proc timeseries, you may get what you want:

data have;

input ID Date :mmddyy10. Amount;

cards;

1 1/1/1990 2

1 1/2/1990 4

1 1/4/1990 3

1 1/5/1990 5

1 1/7/1990 5

1 1/9/1990 10

1 1/10/1990 5

1 1/14/1990 10

;

/*to fillup the blanks*/

proc timeseries data=have out=want;

id date interval=day ;

var amount;

by id;

run;

/*to calculate the moving average*/

proc expand data=want out=outtest;

by ID;

id date;

convert amount= rollave / method=none transformout = (movave 7);

run;

You can always remove the missing value later.

Regards,

Haikuo

Contributor
Posts: 41

## Re: Moving average & proc expand - Quick question

Certainly works - thanks.  Potential problem is my time series data is already 4 gigabytes big (it originally came with missing values which I eliminated to shrink the size) and putting the missing values back just makes it enormous (and takes TONS of time).  I guess if there is no other way, though, then that's what I'll do.

Thanks again.

Super User
Posts: 10,770

## Re: Moving average & proc expand - Quick question

For your situation , I think SQL is the best choice. fast, succinct, easy, convenient ............

Assuming you want WEEKLY mean of amount.

```data have;
input ID Date :mmddyy10. Amount;
format  date mmddyy10.;
cards;
1 1/1/1990 2
1 1/2/1990 4
1 1/4/1990 3
1 1/5/1990 5
1 1/7/1990 5
1 1/9/1990 10
1 1/10/1990 5
1 1/14/1990 10
;
run;

proc sql;
select *,(select mean(amount)
from have as a
where a.date between intnx('week',b.date,-1,'s')+1  and b.date ) as mean
from have as b;
quit;

```

Ksharp

🔒 This topic is solved and locked.