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
;
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
FYI:
data two_pre;
set one ;
temp1=lag1(value);
temp2=lag2(value);
rolling_three_month_value_pre=value+temp1+temp2;
run;
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
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
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;
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
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
Thank you Ksharp,
I have a problem when I runing your code,
I must remove "_temporary_",which will cause error "ERROR: The ARRAYNAME
also I add "retain retain _v1-_v3;",while this is not a big problem.
Thank you!
George
I don't have any problem to run Ksharp's code. Do you want to post your code and log files?
Can you post your code?
The ARRAYNAME
And Did you check the URL posted by Mike too?
Ksharp
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.