Modify Howard's code to meet different requirement

[Linlin]

Below is Howard's code I copied from https://communities.sas.com/post!input.jspa?container=31&containerType=14

data test;

   do value = 2, 5, 3, 1, 10, 7, 5; output; end;

   run;

   data rollsums;

   array vv(3);

   retain vv1-vv3;

   drop vv1-vv3;

   set test;

   vv( mod(_n_-1,3) + 1 ) = value;

   if _n_ GE 3 then rollsum = sum( of vv(*) );

   run;

proc print;run;

                            Obs    value    rollsum

                               1        2         .

                               2        5         .

                               3        3        10

                               4        1         9

                               5       10        14

                               6        7        18

                               7        5        22

How to modify Howard'd code to get:

                           Obs    value    rollsum

                               1        2         .

                               2        5         .

                               3        3        7

                               4        1        8

                               5       10      4

                               6        7       11

                               7        5       17

No lag function please.

Thanks - Linlin

[PGStats · Accepted solution]

Rearrange the sequence of events, use only two-variable array :

data test;
   do value = 2, 5, 3, 1, 10, 7, 5; output; end;
   run;
   data rollsums;
   array vv(2);
   retain vv1-vv2;
   drop vv1-vv2;
   set test;
   if _n_ GE 3 then rollsum = sum( of vv(*) );
   vv( mod(_n_-1,2) + 1 ) = value;
   run;

proc print;run;

PG

[Linlin]

Ooooooops!

data have;

do _n_=1 to 12;

date=(intnx('month','01dec1979'd,_n_,'b'));

return=2+_n_/10;

liquity=4+_n_/10;

Volatility=round((2+_n_/9),.1);

format date mmddyy10.;

output;

end;

run;

data rollmean;

   array rr(6); retain rr1-rr6;

   array ll(6); retain ll1-ll6;

   array vv(6); retain vv1-vv6;

   drop vv1-vv6 rr1-rr6 ll1-ll6;

   set have;

   if _n_ Gt 6 then do;

      r_mean = mean( of rr(*) );

      l_mean = mean( of ll(*) );

      v_mean = round(mean( of vv(*) ),.01);

  end;

   rr( mod(_n_-1,6) + 1 ) = return;

   ll( mod(_n_-1,6) + 1 ) = liquity;

   vv( mod(_n_-1,6) + 1 ) = Volatility;

   run;

Hi  PG and  Haikuo,

which solution is better? this one or the Proc sql we provided: https://communities.sas.com/message/123556#123556

Thanks - Linlin

[PGStats]

Linlin,

The query is slower but easier to figure out (to read, for me at least) and maintain. The query can also be moved over to any SQL server (okay, the date functions will have to be translated). Moreover, the datastep relies on presorting and the presence of one and only one observation per month. I would personnally reserve the datastep approach for cases where the SQL lesser performance creates a problem.

PG

[FriedEgg]

Use lag:

data have;

do _n_=1 to 12;

date=(intnx('month','01dec1979'd,_n_,'b'));

return=2+_n_/10;

liquity=4+_n_/10;

Volatility=round((2+_n_/9),.1);

format date mmddyy10.;

output;

end;

run;

%macro rollmean(n= ,var=);

%substr(&var,1,1)_mean = mean( lag(&var) %do i=2 %to &n; ,lag&i(&var) %end; );

%mend;

data rollmean2;

set have;

%rollmean(n=6 ,var=return);

%rollmean(n=6 ,var=liquity);

%rollmean(n=6 ,var=volatility);

if _n_ lt 7 then call missing(r_mean,l_mean,v_mean);

run;

[MikeZdeb]

hi ... one other suggestion is to use a temporary array (values retained, no extra variables) ...

data rollsums;

array vv(3) _temporary_;

set test;

if _n_ ge 3 then rollsum = sum(of vv(*));

vv(mod(_n_-1,3) + 1 ) = value;

run;

[Linlin]

Thank you Mike!

This one also works:

data test;

   do value = 2, 5, 3, 1, 10, 7, 5; output; end;

   run;

   data rollsums;

   array vv(0:2) _temporary_;

   set test;

   if _n_ GE 3 then rollsum = sum( of vv(*) );

   vv( mod(_n_,3) ) = value;

   run;

proc print;run;

[PGStats]

That's about as fast and compact as it gets! But Linlin, your original requirement was for the sum of the two preceeding months. So the code should be :

data rollsums; 

   array vv(0:1) _temporary_;

   set test;

   if _n_ GT 2 then rollsum = sum( of vv(*) );

   vv( mod(_n_, 2) ) = value;

   run;

PG

[Linlin]

Hi PG,

You are right. Mike's code and my code are for the three proceeding observations.

The code below is the same as Howard's original code.

data test;

   do value = 2, 5, 3, 1, 10, 7, 5; output; end;

   run;

data rollsums_L;

   array vv(0:2) _temporary_;

   set test; 

     vv( mod(_n_,3) ) = value;

   if _n_ gt 2 then rollsum = sum( of vv(*) );

   run;

proc print data=&syslast; title "from Linlin's code";run;

title;

/* Howard's original code */

   data rollsums;

   array vv(3);

   retain vv1-vv3;

   drop vv1-vv3;

   set test;

   vv( mod(_n_-1,3) + 1 ) = value;

   if _n_ GE 3 then rollsum = sum( of vv(*) );

   run;

   proc print;run;

[FriedEgg]

data test;

do value = 2, 5, 3, 1, 10, 7, 5; output; end;

run;

%macro rollmean(n= ,var= ,sdl=work ,sds=);

data rollmean_&sds;

  set &sdl..&sds;

  %substr(&var,1,1)_rollsum = sum( lag(&var) %do i=2 %to &n; ,lag&i(&var) %end; );

  if _n_<&n then call missing(%substr(&var,1,1)_rollsum);

run;

%mend;

%rollmean(n=3 ,var=value ,sds=test);

2	.
5	.
3	7
1	10
10	9
7	14
5	18