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;
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.
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
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.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.