BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Solph
Pyrite | Level 9

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

8 REPLIES 8
ChrisNZ
Tourmaline | Level 20

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

 

 

Reeza
Super User

Is there a specific reason you're using macros?

Solph
Pyrite | Level 9

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. 

Reeza
Super User

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

Solph
Pyrite | Level 9

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;

 

Reeza
Super User

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. 

 

 

ChrisNZ
Tourmaline | Level 20

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.

Solph
Pyrite | Level 9

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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