topic Re: Help with Moving Sum in SAS Programming

Help with Moving Sum

hhchenfx — Wed, 02 Dec 2020 17:17:34 GMT

Hi, My code calculate moving sum (i to i+3). The last 2 rows have issue where it double count the last value. Can you please help me to fix it? Thanks, HHC


data have;
input value;
datalines;
1
2
3
4
5
6
7
8
9
10
;run;

data want; 
set have nobs=totalobs;
i+1;
total=0;
do j=i to i+2;
  set have (keep = value ) point=j nobs=nobs;;
  total= total + value;
  *output; 
end;
run;

data have;
input value;
datalines;
1
2
3
4
5
6
7
8
9
10
;run;

data want;
set have nobs=totalobs;
i+1;
total=0;
do j=i to min(i+2,totalobs);
set have (keep = value rename =(value=v)) point=j ;
total= total + v;
*output;
end;
run;

Re: Help with Moving Sum

ballardw — Wed, 25 Nov 2020 23:32:49 GMT

Please show what you expect/want for output.

Do you thing the bit in your log that looks like this might have some bearing?

totalobs=10 value=10 i=9 total=29 j=12 nobs=10 _ERROR_=1 _N_=9
totalobs=10 value=10 i=10 total=30 j=13 nobs=10 _ERROR_=1 _N_=10

When POINT exceeds the number of records in the data set what do you think happens?

Maybe (guessing)

data want2; 
set have nobs=totalobs;
i+1;
total=0;
do j=i to i+2;
  set have (keep = value ) point=j nobs=nobs;
  if j>nobs then stop;
  total= total + value;
  *output; 
end;
run;

Re: Help with Moving Sum

novinosrin — Thu, 26 Nov 2020 01:22:51 GMT

Perhaps-

do j=i to min(i+2,totalobs);

Re: Help with Moving Sum

ballardw — Thu, 26 Nov 2020 01:29:24 GMT

@novinosrin wrote:

Perhaps-
do j=i to min(i+2,totalobs);
?

I looked at that too. One of the reasons I asked for expected out come.

Re: Help with Moving Sum

Ksharp — Thu, 26 Nov 2020 12:24:25 GMT

data have;
input value;
datalines;
1
2
3
4
5
6
7
8
9
10
;run;
data want;
 merge have 
       have(firstobs=2 keep=value rename=(value=value1))
       have(firstobs=3 keep=value rename=(value=value2)) ;
total=sum(value,value1,value2);
run;

Re: Help with Moving Sum

Kurt_Bremser — Thu, 26 Nov 2020 12:39:54 GMT

Since you need to look ahead, @Ksharp 's answer is the way to go. if you wanted to "look back", a temporary array would be best:

%let size=3;

data want;
set have;
array window {0:%eval(&size.-1)} _temporary_;
window{mod(_n_,&size.)} = value;
total = sum(of window{*});
run;