topic get moving average with multiple by-groups in SAS Procedures

get moving average with multiple by-groups

podarum — Tue, 28 Feb 2012 19:17:08 GMT

I'm using this code below, but I'd like to add 2 more by-groups.. ? Thanks

data by

patient;

retain num_sum 0

if first.patient then do;

count=0;

num sum=0;

end;

copunt+1;

last&n=lag&n(num);

if count gt &n then num_sum=sum(num_sum,num,-last&n);

else num_sum=sum(num_sum,num);

if count ge &n then mov_aver=num_sum/&n;

else mov_aver=.;

run;

Re: get moving average with multiple by-groups

Reeza — Tue, 28 Feb 2012 19:46:06 GMT

Do you have ETS licensed?

If so check out proc expand and the transformation example.

get moving average with multiple by-groups

podarum — Tue, 28 Feb 2012 19:49:08 GMT

Yes Reeza

Re: get moving average with multiple by-groups

Reeza — Tue, 28 Feb 2012 19:50:26 GMT

Maybe this will help?

http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_expand_sect034.htm

get moving average with multiple by-groups

podarum — Tue, 28 Feb 2012 19:52:36 GMT

Thanks, that's actually a good approach too. but wonder if I can use a by statement

get moving average with multiple by-groups

Reeza — Tue, 28 Feb 2012 21:02:16 GMT

Depends on what you're looking for as always.

You can set multiple values within a by statement, but they end up 'nested' rather than multiple level. Perhaps some sample data and expected results.

get moving average with multiple by-groups

podarum — Wed, 29 Feb 2012 20:31:28 GMT

here is a sample code that I'm trying.. My goal is to get a 6 month moving average by both the FSA and Type. But all I'm getting is by FSA, and it ignores where the Property starts.

%let n = 6;

data out_data;

set in_data;

by FSA Type;

retain price_sum 0;

if first.FSA and first.Type then do;

count=0;

price_sum=0;

end;

count+1;

last&n=lag&n(price);

if count gt &n then price_sum=sum(price_sum,price,-last&n);

else price_sum=sum(price_sum,price);

if count ge &n then mov_aver=price_sum/&n;

else mov_aver=.;

run;

Sample Data, to do a 2 month rolling average for simplicity:

FSA Type Price Date

B1A A 2 2001.01

B1A A 3 2001.02

B1A A 4 2001.03

B1A B 1 2001.01

B1A B 2 2001.02

B1A B 3 2001.03

B2M A 6 2002.03

B2M A 7 2002.04

B2M B 1 2002.03

B2M B 2 2002.04

Expect to get:

FSA Type Price Date Mov_Avg

B1A A 2 2001.01 -

B1A A 3 2001.02 2.5

B1A A 4 2001.03 3.5

B1A B 1 2001.01 -

B1A B 2 2001.02 1.5

B1A B 3 2001.03 2.5

B2M A 6 2002.03 -

B2M A 7 2002.04 6.5

B2M B 1 2002.03 -

B2M B 2 2002.04 1.5

get moving average with multiple by-groups

Ksharp — Thu, 01 Mar 2012 10:25:40 GMT

Then Stack Skill is very helpful. It is from Arthur.Carpenter .

data x;
input FSA $     Type   $     Price         Date $ ;
datalines;
B1A         A              2             2001.01
B1A         A              3             2001.02
B1A         A              4             2001.03
B1A         B              1             2001.01
B1A         B              2             2001.02
B1A         B              3             2001.03
B2M        A               6            2002.03
B2M        A               7            2002.04
B2M        B               1            2002.03
B2M        B               2            2002.04
;
run;
data want;
 set x;
 array p{2} _temporary_;
 if type ne lag(type) then call missing(of p{*});
 p{mod(_n_,2)+1}=price;
 mov_avg=mean(of p{*});
 if type ne lag(type) then call missing(mov_avg);
run;

Ksharp