11-16-2017 05:25 PM - edited 11-16-2017 05:38 PM
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;
11-16-2017 08:24 PM
11-16-2017 06:16 PM
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;
11-16-2017 06:33 PM - edited 11-16-2017 06:34 PM
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.
11-16-2017 07:24 PM
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;
11-16-2017 07:58 PM
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.
11-16-2017 08:24 PM
11-16-2017 09:09 PM
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;
Need further help from the community? Please ask a new question.