topic Re: Modify Howard's code to meet different requirement in SAS Programming

Modify Howard's code to meet different requirement

Linlin — Mon, 16 Apr 2012 17:12:04 GMT

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

Re: Modify Howard's code to meet different requirement

PGStats — Mon, 16 Apr 2012 17:21:26 GMT

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;

Re: Modify Howard's code to meet different requirement

Linlin — Mon, 16 Apr 2012 18:00:23 GMT

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

Re: Modify Howard's code to meet different requirement

PGStats — Mon, 16 Apr 2012 19:14:15 GMT

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.

Re: Modify Howard's code to meet different requirement

FriedEgg — Mon, 16 Apr 2012 19:27:10 GMT

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;

Re: Modify Howard's code to meet different requirement

MikeZdeb — Mon, 16 Apr 2012 19:27:39 GMT

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;

Re: Modify Howard's code to meet different requirement

Linlin — Mon, 16 Apr 2012 20:19:55 GMT

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;

Re: Modify Howard's code to meet different requirement

PGStats — Mon, 16 Apr 2012 20:28:54 GMT

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;

Re: Modify Howard's code to meet different requirement

Linlin — Mon, 16 Apr 2012 21:01:30 GMT

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;

Re: Modify Howard's code to meet different requirement

FriedEgg — Mon, 16 Apr 2012 22:00:55 GMT

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