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

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
;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

10 REPLIES 10
George_S
Fluorite | Level 6


FYI:

data two_pre;

set one ;

  temp1=lag1(value);

   temp2=lag2(value);

rolling_three_month_value_pre=value+temp1+temp2;

run;

Linlin
Lapis Lazuli | Level 10

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

PGStats
Opal | Level 21

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
George_S
Fluorite | Level 6

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;

PGStats
Opal | Level 21

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
Ksharp
Super User
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

George_S
Fluorite | Level 6

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

    Linlin
    Lapis Lazuli | Level 10

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

    Ksharp
    Super User

    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

    MikeZdeb
    Rhodochrosite | Level 12

    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

    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!

    What is Bayesian Analysis?

    Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

    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
    • 10 replies
    • 1245 views
    • 9 likes
    • 5 in conversation