## Help with moving average to replicate Excel approach

Solved
Super Contributor
Posts: 506

# Help with moving average to replicate Excel approach

Hi Everyone,

This code below include 2 moving average rules.

The first rule is applied when _n_=3:

avrg_gain=sum(l1,l2,GAIN)/3;

The second rule is applied when _n_>3:

avrg_gain=(lag_avrg_gain*3+gain)/3;

This code works but I want to put in Macro so I can change number of record to get average, say 4, 5...

Thank you,

HHC

data have;

input gain loss;

datalines;

1 2

2 3

3 4

4 2

5 1

6 9

7 0

8 1

;run;

data want; set have;

drop l1-l2 ln1-ln2;

l1=lag1(gain);l2=lag2(gain);

ln1=lag1(loss);ln2=lag2(loss);

retain lag_avrg_gain lag_avrg_loss;

if _N_=3 then do;

avrg_gain=sum(l1,l2,GAIN)/3;

avrg_loss=sum(ln1,ln2,LOSS)/3;

end;

if _N_>3 then do;

avrg_gain=(lag_avrg_gain*3+gain)/3;

avrg_loss=(lag_avrg_loss*3+loss)/3;

end;

lag_avrg_gain=avrg_gain;

lag_avrg_loss=avrg_loss;

run;

Accepted Solutions
Solution
‎02-27-2015 04:09 AM
PROC Star
Posts: 2,369

## Re: Help with moving average to replicate Excel approach

This could written in a slightly simpler manner:

%macro calculation(nbrec=);

data WANT;

set HAVE;

drop LAG_GAIN1-LAG_GAIN&nbrec;

retain LAG_AVRG_GAIN LAG_AVRG_LOSS;

%do i=1 %to &nbrec.;

LAG_GAIN&i.=lag&i.(GAIN);

LAG_LOSS&i.=lag&i.(LOSS);

%end;

if _N_=&nbrec. then do;

 AVRG_GAIN=sum(of LAG_GAIN1-LAG_GAIN%eval(&nbrec.-1),GAIN)/&nbrec.; AVRG_LOSS=sum(of LAG_LOSS1-LAG_LOSS%eval(&nbrec.-1),LOSS)/&nbrec.;

end;

if _N_>&nbrec. then do;

AVRG_GAIN=(LAG_AVRG_GAIN*&nbrec.+GAIN)/&nbrec.;

AVRG_LOSS=(LAG_AVRG_LOSS*&nbrec.+LOSS)/&nbrec.;

end;

LAG_AVRG_GAIN=AVRG_GAIN;

LAG_AVRG_LOSS=AVRG_LOSS;

run;

%mend;

I doubt this is correct though. You should probably divide by  (&nbrec.+1)  if _N_>&nbrec.

All Replies
Frequent Contributor
Posts: 146

## Re: Help with moving average to replicate Excel approach

Hi,

You can do the lag variables doing

%macro calculation(num_records=);

data want; set have;

drop l1-l20 ln1-ln20;

retain lag_avrg_gain lag_avrg_lo

%do i=1 %to &num_records;

l&i.=lag&i.(grain);

%end;

IF _N_=&num_records then do;

avrg_gain=sum(%do i=1 %to %eval(&num_records-1);

l&i.,

%end;GAIN)/&num_records;

avrg_loss=sum(%do i=1 %to %eval(&num_records-1);

ln&i.,

%end;LOSS)/&num_records;

end;

if _N_>&num_records then do;

avrg_gain=(lag_avrg_gain*&num_records+gain)/&num_records;

avrg_loss=(lag_avrg_loss*&num_records+loss)/&num_records;

end;

lag_avrg_gain=avrg_gain;

lag_avrg_loss=avrg_loss;

run;

%mend;

Sorry for the color, I don't know how put the sames that SAS

Solution
‎02-27-2015 04:09 AM
PROC Star
Posts: 2,369

## Re: Help with moving average to replicate Excel approach

This could written in a slightly simpler manner:

%macro calculation(nbrec=);

data WANT;

set HAVE;

drop LAG_GAIN1-LAG_GAIN&nbrec;

retain LAG_AVRG_GAIN LAG_AVRG_LOSS;

%do i=1 %to &nbrec.;

LAG_GAIN&i.=lag&i.(GAIN);

LAG_LOSS&i.=lag&i.(LOSS);

%end;

if _N_=&nbrec. then do;

 AVRG_GAIN=sum(of LAG_GAIN1-LAG_GAIN%eval(&nbrec.-1),GAIN)/&nbrec.; AVRG_LOSS=sum(of LAG_LOSS1-LAG_LOSS%eval(&nbrec.-1),LOSS)/&nbrec.;

end;

if _N_>&nbrec. then do;

AVRG_GAIN=(LAG_AVRG_GAIN*&nbrec.+GAIN)/&nbrec.;

AVRG_LOSS=(LAG_AVRG_LOSS*&nbrec.+LOSS)/&nbrec.;

end;

LAG_AVRG_GAIN=AVRG_GAIN;

LAG_AVRG_LOSS=AVRG_LOSS;

run;

%mend;

I doubt this is correct though. You should probably divide by  (&nbrec.+1)  if _N_>&nbrec.

Super User
Posts: 9,599

## Re: Help with moving average to replicate Excel approach

Hi,

Sorry, its not very clear for me.  I have looked at your code, to which there are several mistakes, e.g. missing semi-colon after retain statement, plus many non-initialised variables.  I am not clear on the logic either, why for instance is a calculation only done at observation number 3, what happens if the data is not sorted as you expect it to be, or there is only 2 observations?  If you really are only dealing with 8 instances, then you may be better off working with arrays and a macro variable (i.e. data is horizontal rather than vertical):

%let Num_Iterations=4;

data want;

set have;

array ln{*};

array l{*};

avg=sum(of l1-l3);

...;

run;

Please show what you want out.

Super Contributor
Posts: 506

## Re: Help with moving average to replicate Excel approach

I correct that error.

I have to sort data prior to processing.

The calculate start at 3 as a rule I have to follow. For the macro, I can do it with 4, 5....

HHC

🔒 This topic is solved and locked.