Solved
Contributor
Posts: 35

# Rolling calculation dataset

Hello everyone, dataset two is needed.

How to get the new colomn rolling_three_month_value in a simple way?

Thanks!

two:

month  value    rolling_three_month_value

12       100          100+200+150

11        200          200+150+180

10        150          150+180+290

9          180          180+290+410

8          290           290+410+360

7          410          .....so and so forth....

6         360

5         700

4         850

3         220

2         255

1         600

data one;
input month  value ;
cards;

12       100

11        200

10        150

9          180

8          290

7          410

6         360

5         700

4         850

3         220

2         255

1         600
;

Accepted Solutions
Solution
‎04-11-2012 05:43 PM
Posts: 5,543

## Re: Rolling calculation dataset

proc sql;

create table two as

select  A.month, A.value, sum(b.value) as rollingValue

from one as A inner join one as B on B.month between A.month and A.month-2

group by A.month, A.value

order by A.month desc;

select * from two;

quit;

PG

PG

All Replies
Contributor
Posts: 35

## Re: Rolling calculation dataset

FYI:

data two_pre;

set one ;

temp1=lag1(value);

temp2=lag2(value);

rolling_three_month_value_pre=value+temp1+temp2;

run;

Super Contributor
Posts: 1,636

## Re: Rolling calculation dataset

data one;

input month  value ;

cards;

12       100

11        200

10        150

9          180

8          290

7          410

6         360

5         700

4         850

3         220

2         255

1         600

;

proc sort data=one;

by month;

data two;

set one;

rolling_three_month_value=value+lag(value)+lag2(value);

proc sort;

by descending month;

run;

proc print;run;

rolling_

three_month_

Obs    month    value        value

1      12      100          450

2      11      200          530

3      10      150          620

4       9      180          880

5       8      290         1060

6       7      410         1470

7       6      360         1910

8       5      700         1770

9       4      850         1325

10       3      220         1075

11       2      255            .

12       1      600            .

or

data two;

set one;

rolling_three_month_value=sum(of value,lag(value),lag2(value));

proc sort;

by descending month;

run;

proc print;run;

rolling_

three_month_

Obs    month    value        value

1      12      100          450

2      11      200          530

3      10      150          620

4       9      180          880

5       8      290         1060

6       7      410         1470

7       6      360         1910

8       5      700         1770

9       4      850         1325

10       3      220         1075

11       2      255          855

12       1      600          600

Message was edited by: Linlin

Solution
‎04-11-2012 05:43 PM
Posts: 5,543

## Re: Rolling calculation dataset

proc sql;

create table two as

select  A.month, A.value, sum(b.value) as rollingValue

from one as A inner join one as B on B.month between A.month and A.month-2

group by A.month, A.value

order by A.month desc;

select * from two;

quit;

PG

PG
Contributor
Posts: 35

## Re: Rolling calculation dataset

what diference?

proc sql;

create table two as

select  distinct A.month, A.value, sum(b.value) as rollingValue

from one as A inner join one as B on B.month between A.month and A.month-2

group by A.month/*, A.value*/

order by A.month desc;

select * from two;

quit;

Posts: 5,543

## Re: Rolling calculation dataset

Same result. But more work to do with your version. The summarized data must be remerged with the original dataset and the result scanned for duplicates. You will notice an extra message in the Log when running your version. Some SQL processors, i.e. MS-SQL-Jet, do not even accept remerging queries.

BTW, I realized after posting my answer that it might be more appropriate to use  B.month between A.month-2 and A.month

PG

PG
Super User
Posts: 10,788

## Re: Rolling calculation dataset

```data one;
input month  value ;
cards;
12       100
11        200
10        150
9          180
8          290
7          410
6         360
5         700
4         850
3         220
2         255
1         600
;
run;
data temp(keep=return);
set one;
array _v{0:2} _temporary_;
_v{mod(_n_,3)}=value;
if _n_ ge 3 then do;return=sum(of _v{*});output;end;
run;
data want;
merge one temp;
run;

```

Ksharp

Contributor
Posts: 35

## Re: Rolling calculation dataset

Thank you Ksharp,

I have a problem when I runing your code,

I must remove  "_temporary_",which will cause error "ERROR: The ARRAYNAME

• specification requires a variable based array."  I don't understand it.
• also I add "retain retain  _v1-_v3;",while this is not a big problem.

Thank you!

George

Super Contributor
Posts: 1,636

## Re: Rolling calculation dataset

I don't have any problem to run Ksharp's code. Do you want to post your code and log files?

Super User
Posts: 10,788

## Re: Rolling calculation dataset

The ARRAYNAME

• specification requires a variable based array. It is not suited for _temporary_ array.
• And Did you check the URL posted by Mike too?

Ksharp

Valued Guide
Posts: 765

## Re: Rolling calculation dataset

hi ... Ksharp's neat solution has been much discussed on SAS-L

for some more reading ... http://listserv.uga.edu/cgi-bin/wa?A2=ind0804B&L=sas-l&D=0&P=29162

🔒 This topic is solved and locked.