Help with moving average to replicate Excel approach

[hhchenfx]

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...

Could you please help me with that?

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;

[arodriguez]

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

[ChrisNZ · Accepted solution]

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.

[RW9]

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.

[hhchenfx]

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