topic Re: Rolling calculation dataset in SAS Procedures

Rolling calculation dataset

George_S — Wed, 11 Apr 2012 21:12:16 GMT

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
;

Re: Rolling calculation dataset

George_S — Wed, 11 Apr 2012 21:37:18 GMT

FYI:

data two_pre;

set one ;

temp1=lag1(value);

temp2=lag2(value);

rolling_three_month_value_pre=value+temp1+temp2;

run;

Re: Rolling calculation dataset

Linlin — Wed, 11 Apr 2012 21:40:49 GMT

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 .

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

Re: Rolling calculation dataset

PGStats — Wed, 11 Apr 2012 21:43:35 GMT

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;

Re: Rolling calculation dataset

George_S — Thu, 12 Apr 2012 01:24:18 GMT

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;

Re: Rolling calculation dataset

PGStats — Thu, 12 Apr 2012 02:05:19 GMT

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

Re: Rolling calculation dataset

Ksharp — Thu, 12 Apr 2012 05:17:50 GMT

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

Re: Rolling calculation dataset

MikeZdeb — Thu, 12 Apr 2012 15:39:33 GMT

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

Re: Rolling calculation dataset

George_S — Fri, 13 Apr 2012 17:50:19 GMT

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

Re: Rolling calculation dataset

Linlin — Fri, 13 Apr 2012 18:19:40 GMT

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

Re: Rolling calculation dataset

Ksharp — Mon, 16 Apr 2012 09:02:11 GMT

Can you post your code?

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