DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 109
Accepted Solution

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

[ Edited ]

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;

 


Accepted Solutions
Solution
‎11-16-2017 09:09 PM
PROC Star
Posts: 2,316

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

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.

View solution in original post


All Replies
PROC Star
Posts: 2,316

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

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

 

 

Super User
Posts: 23,262

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

Is there a specific reason you're using macros?

Frequent Contributor
Posts: 109

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

[ Edited ]

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. 

Super User
Posts: 23,262

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

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

Frequent Contributor
Posts: 109

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

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;

 

Super User
Posts: 23,262

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

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]);

vs 

  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. 

 

 

Solution
‎11-16-2017 09:09 PM
PROC Star
Posts: 2,316

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

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.

Frequent Contributor
Posts: 109

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

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;
☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 250 views
  • 0 likes
  • 3 in conversation