BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hhchenfx
Barite | Level 11

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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.

View solution in original post

4 REPLIES 4
arodriguez
Lapis Lazuli | Level 10

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
Tourmaline | Level 20

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 514 views
  • 4 likes
  • 4 in conversation