get moving average with multiple by-groups

[podarum]

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;

[Reeza]

Do you have ETS licensed?

If so check out proc expand and the transformation example.

[podarum]

Yes Reeza

[Reeza]

Maybe this will help?

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

[podarum]

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

[Reeza]

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.

[podarum]

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

[Ksharp]

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