## Modify Howard's code to meet different requirement

Solved
Super Contributor
Posts: 1,636

# 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
Posts: 5,540

## 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

All Replies
Solution
‎04-16-2012 01:21 PM
Posts: 5,540

## 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

Posts: 5,540

## 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
Posts: 1,318

## 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;

Posts: 5,540

## 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;

Posts: 1,318

## 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 1 10 10 9 7 14 5 18
🔒 This topic is solved and locked.

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

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