topic Re: Adding values in a variable based on the values in the other variable in SAS Programming

Adding values in a variable based on the values in the other variable

Tommer — Sun, 06 Sep 2020 02:59:53 GMT

Hi, I am trying to add the values of mnth in the order of code starting from 1 to 5, the 2 to 6, 3 to 7 etc. Any help in doing this?

data score;
input code mnth yr;
datalines;

1 1.1 25
2 1.2 26
3 1.3 25
4 2.1 24
5 3.2 26
6 3.4 67
7 5.6 56
8 6 34
9 7 45

9 7 45
;

run;

Kurt_Bremser — Sun, 06 Sep 2020 06:34:24 GMT

What shoild the output look like in terms of variables and observations?

Should it start the first time the rolling window is filled with 5 values, or should the months before that appear with the sum of 1,2,3,4 values?

Tommer — Mon, 07 Sep 2020 01:03:32 GMT

@Kurt_Bremser

It should start the first time the rolling window is filled with 5 values.
Ex:
Roll1to5 8.9
Roll2to6 11.2
Roll3to6 ......etc.

Kurt_Bremser — Mon, 07 Sep 2020 08:25:09 GMT

First, we need to make sure we only have one observation per code:

proc summary data=score;
by code;
var mth;
output out=comp (drop=_type_ _freq_) sum()=;
run;

Then, we create the rolling window with a temporary array, and start outputting once it is filled:

data want;
set comp;
array vals {5} _temporary_;
vals{mod(_n_,5)+1} = mnth;
if _n_ ge 5;
mnth = sum (of vals{*});
keep code mnth;
run;