BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yeaforme
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

View solution in original post

3 REPLIES 3
Haikuo
Onyx | Level 15

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

yeaforme
Calcite | Level 5

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.

Ksharp
Super User

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

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!

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.

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
  • 3 replies
  • 2451 views
  • 0 likes
  • 3 in conversation