topic Re: Calculating sum for every x rolling months (for horizontal data) in SAS Programming

Calculating sum for every x rolling months (for horizontal data)

Solph — Thu, 16 Nov 2017 22:38:38 GMT

Hi,

I have a horizontal data that’s measured on monthly bases for 5 months. I wanted to calculate a moving sum based on every 2 months. Here is my code, why doesn’t it work?

Thanks in advanced. Here is the data I have.

data have;
input id psw1 psw2 psw3 psw4 psw5;
datalines;
1 10 20 30 10 10
2 10 30 40 10 20
;
run;

Here is the data I want:

data want;
input id psw1 psw2 psw3 psw4 psw5 sumvalue1 sumvalue2 sumvalue3 sumvalue4 ;
datalines;
1 10 20 30 10 10 30 50 40 20 
2 10 30 40 10 20 40 70 50 30
;
run;

Here is my code:

data want;
set have;
array value(5) psw1-psw5;
array sumvalue(4) sumvalue1-sumvalue4;
%macro get();
%do i=1 %to 4;
%let i2=%eval(&i.+1); 
Sumvalue(&i) =sum(of value(&i)-value(&i2));
%end;
%mend;
%get;
run;

Re: Calculating sum for every x rolling months (for horizontal data)

ChrisNZ — Thu, 16 Nov 2017 23:16:29 GMT

No need for macros.

data have;
input id psw1 psw2 psw3 psw4 psw5;
datalines;
1 10 20 30 10 10
2 10 30 40 10 20
run;

data WANT;
  set HAVE;
  array VALUE(5) PSW1-PSW5;
  array SUMVALUE(4) SUMVALUE1-SUMVALUE4;
  do I=1 to 4;
   SUMVALUE(I) = VALUE[I]+VALUE[I+1];
  end;
run;

id	psw1	psw2	psw3	psw4	psw5	SUMVALUE1	SUMVALUE2	SUMVALUE3	SUMVALUE4
1	10	20	30	10	10	30	50	40	20
2	10	30	40	10	20	40	70	50	30

Re: Calculating sum for every x rolling months (for horizontal data)

Reeza — Thu, 16 Nov 2017 23:17:15 GMT

Is there a specific reason you're using macros?

Re: Calculating sum for every x rolling months (for horizontal data)

Solph — Thu, 16 Nov 2017 23:34:45 GMT

ChrisNZ's code worked. But there is a reason for macro. My actual data has PSW measured on a daily basis for a whole year, so I've psw1-psw365 (not just psw1-5 in the example). And I wanted to calculate sum based on every 30 days. So my Want data would have 365 sum values (i.e. Sumvalue 1 - Sumvalue335).

I guess I can write then out as sumvalue(i) = value(i) + value(i+1) + value(i+2) + value(i+3) ..... + value(i+29).

But I wondered why when I wrote sumvalue(i) = sum(of value(i) - value(i+29), it didn't work. I assumed the issue lies in i+29. So I used %eval and the macro.

But the macro didn't seem to accept the sum of function.

Re: Calculating sum for every x rolling months (for horizontal data)

Reeza — Thu, 16 Nov 2017 23:45:23 GMT

It's still a fixed number so there's no need for macros. The indexes for an array will handle it fine.

Re: Calculating sum for every x rolling months (for horizontal data)

Solph — Fri, 17 Nov 2017 00:24:12 GMT

I expanded the data (to 30 variables, psw1-psw30) and applied the code for sum of every 3 rolling days, in two ways:

Code 1: use sumvalue(i) = value(i)+value(i+1)+value(i+2). It works.

Code 2: use sumvalue(i) = sum(of value(i) - value(i+2)), using sum off but it didn't work. The error message said "ERROR 22-322: Syntax error, expecting one of the following: ), ','. "

It would be great if it's a simple fix for code 2. Otherwise I'll stick to code 1 and expand to write each component up to value(i+29). Thanks.

Here is the expanded data and code 1.

data have;
input id psw1 psw2 psw3 psw4 psw5 psw6 psw7 psw8 psw9 psw10
psw11 psw12 psw13 psw14 psw15 psw16 psw17 psw18 psw19 psw20
psw21 psw22 psw23 psw24 psw25 psw26 psw27 psw28 psw29 psw30;
datalines;
1 1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 7 8 9 1 2 3
2 5 6 7 8 9 1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 7
;
run;
*Code 1;
data WANT;
  set HAVE;
  array value(30) PSW1-PSW30;
  array SumValue (28) SumValue1 - SumValue28;
  do i=1 to 28;
   SumValue(i) = value[i]+value[i+1]+value[i+2];
  end;
run;
proc print noobs; var psw1-psw30; run;
proc print noobs; var sumvalue1-SumValue28; run;

Here is code 2 that didn't work.

*Code 2;
data WANT;
  set HAVE;
  array value(30) PSW1-PSW30;
  array SumValue (28) SumValue1 - SumValue28;
  do i=1 to 28;
   SumValue(i) = sum(of value[i]-value[i+2]);
  end;
run;

Re: Calculating sum for every x rolling months (for horizontal data)

Reeza — Fri, 17 Nov 2017 00:58:28 GMT

Your formula's aren't the same so why would you expect them to be the same?

Doesn't work - and you can't use OF with the array indices in that manner

SumValue(i) = sum(of value[i]-value[i+2]);

  SumValue(i) = value[i]+value[i+1]+value[i+2];

If there's a chance you have missing data, you want to use the SUM() function rather than +, with + any missing included sets it to missing.

  SumValue(i) = sum(value[i], value[i+1], value[i+2]);

I would consider adding a nested inner loop to do the sum so it's more dynamic. That way if you're summing a window of 2 or 20 it doesn't matter.

Re: Calculating sum for every x rolling months (for horizontal data)

ChrisNZ — Fri, 17 Nov 2017 01:24:41 GMT

In this case you do need macro, and the formula you want is probably

SUM&i= sum( of VALUE&i. - VALUE%eval(&i+2) );

No need for an array.

Re: Calculating sum for every x rolling months (for horizontal data)

Solph — Fri, 17 Nov 2017 02:09:38 GMT

Thanks Reeza. I guess sum(of psw1-psw28) works, but not when array is used (e.g. sum(of value(i)-value(i+2).

Thanks ChrisNZ. It worked. Thanks a lot.

%macro get();
data WANT;
  set HAVE;
  %do i=1 %to 28;
   SumValue&i = sum(of psw&i - psw%eval(&i+2));
  %end;
run;
%mend;
%get;