DATA Step, Macro, Functions and more

Modify Howard's code to meet different requirement

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,636
Accepted Solution

Modify Howard's code to meet different requirement

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


Accepted Solutions
Solution
‎04-16-2012 01:21 PM
Respected Advisor
Posts: 4,644

Re: Modify Howard's code to meet different requirement

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

PG

View solution in original post


All Replies
Solution
‎04-16-2012 01:21 PM
Respected Advisor
Posts: 4,644

Re: Modify Howard's code to meet different requirement

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

PG
Super Contributor
Posts: 1,636

Re: Modify Howard's code to meet different requirement

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

Respected Advisor
Posts: 4,644

Re: Modify Howard's code to meet different requirement

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

PG
Trusted Advisor
Posts: 1,300

Re: Modify Howard's code to meet different requirement

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;

Valued Guide
Posts: 765

Re: Modify Howard's code to meet different requirement

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;

Super Contributor
Posts: 1,636

Re: Modify Howard's code to meet different requirement

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;

Respected Advisor
Posts: 4,644

Re: Modify Howard's code to meet different requirement


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

PG
Super Contributor
Posts: 1,636

Re: Modify Howard's code to meet different requirement

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;

Trusted Advisor
Posts: 1,300

Re: Modify Howard's code to meet different requirement

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
110
10 9
714
518
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 336 views
  • 3 likes
  • 4 in conversation