Help using Base SAS procedures

Rolling calculation dataset

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

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
Respected Advisor
Posts: 4,927

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

View solution in original post


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
Respected Advisor
Posts: 4,927

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;

Respected Advisor
Posts: 4,927

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,035

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,035

    Re: Rolling calculation dataset

    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

    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.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 10 replies
    • 469 views
    • 9 likes
    • 5 in conversation