## get moving average with multiple by-groups

Super Contributor
Posts: 409

# get moving average with multiple by-groups

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;

Super User
Posts: 23,677

## Re: get moving average with multiple by-groups

If so check out proc expand and the transformation example.

Super Contributor
Posts: 409

Yes Reeza

Super User
Posts: 23,677

## Re: get moving average with multiple by-groups

Super Contributor
Posts: 409

## get moving average with multiple by-groups

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

Super User
Posts: 23,677

## get moving average with multiple by-groups

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.

Super Contributor
Posts: 409

## get moving average with multiple by-groups

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

Super User
Posts: 10,766

## get moving average with multiple by-groups

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

Discussion stats
• 7 replies
• 976 views
• 3 likes
• 3 in conversation