topic Re: Help with moving average to replicate Excel approach in SAS Programming

Help with moving average to replicate Excel approach

hhchenfx — Fri, 27 Feb 2015 00:55:10 GMT

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;

Re: Help with moving average to replicate Excel approach

arodriguez — Fri, 27 Feb 2015 07:14:44 GMT

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

Re: Help with moving average to replicate Excel approach

ChrisNZ — Fri, 27 Feb 2015 09:09:20 GMT

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.

Re: Help with moving average to replicate Excel approach

RW9 — Fri, 27 Feb 2015 09:30:05 GMT

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.

Re: Help with moving average to replicate Excel approach

hhchenfx — Fri, 27 Feb 2015 14:50:04 GMT

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