Good day
I have a task in creating a sum given the followng
ColA ColB
1 4
2 8
3 12
4 10
5 20
6 30
7 11
8 13
9 14
10 60
11 70
12 100
Frist i want to calculate from ColA frist 4 rows but on b from the 5th row in a manner below.
ColB divided by ColA
Sum(20,30,11,13)/Sum(1,2,3,4)
Sum(30,11,13,14)/Sum(2,3,4,5)
Sum(11,13,14,60)/Sum(3,4,5,6) and so forth.
however i want to do this regardless of the number of row i also need a trigger to show the last row and it should stop.
Your help will be highly appreciated.
Hi @langalife ,
do you mean something like below:
data have;
input ColA ColB;
cards;
1 4
2 8
3 12
4 10
5 20
6 30
7 11
8 13
9 14
10 60
11 70
12 100
;
run;
data want;
set have;
sum_a = 0;
sum_a + (colA + lag1(colA) + lag2(colA) + lag3(colA));
if _N_ >= 4;
merge
have(keep=colB rename=(colB=colNB)firstobs=5)
have(keep=colB rename=(colB=colNBB)firstobs=6)
have(keep=colB rename=(colB=colNBBB)firstobs=7)
have(keep=colB rename=(colB=colNBBBB)firstobs=8)
;
sum_b = sum(of colNB:);
drop colNB:;
avg = sum_b / sum_a;
run;
proc print;
run;
data want2;
set have(obs=3) want;
run;
proc print;
run;
?
All the best
Bart
@langalife Hi and welcome to the SAS Community 🙂
Do you need this to handle By-Groups? And do you have a SAS/ETS license?
Hi @langalife ,
do you mean something like below:
data have;
input ColA ColB;
cards;
1 4
2 8
3 12
4 10
5 20
6 30
7 11
8 13
9 14
10 60
11 70
12 100
;
run;
data want;
set have;
sum_a = 0;
sum_a + (colA + lag1(colA) + lag2(colA) + lag3(colA));
if _N_ >= 4;
merge
have(keep=colB rename=(colB=colNB)firstobs=5)
have(keep=colB rename=(colB=colNBB)firstobs=6)
have(keep=colB rename=(colB=colNBBB)firstobs=7)
have(keep=colB rename=(colB=colNBBBB)firstobs=8)
;
sum_b = sum(of colNB:);
drop colNB:;
avg = sum_b / sum_a;
run;
proc print;
run;
data want2;
set have(obs=3) want;
run;
proc print;
run;
?
All the best
Bart
One way ..
data have;
input ColA ColB;
datalines;
1 4
2 8
3 12
4 10
5 20
6 30
7 11
8 13
9 14
10 60
11 70
12 100
;
data want(keep=colA colB avg);
array l [0:3] _temporary_;
do obs=1 by 1 until (lr);
merge have
have(keep=colB rename=(colB=colB2)firstobs=2)
have(keep=colB rename=(colB=colB3)firstobs=3)
have(keep=colB rename=(colB=colB4)firstobs=4) end=lr;
l [mod(obs, 4)] = lag1(ColA);
avg = sum(of colB:) / sum(of l [*]);
output;
end;
run;
data have;
input ColA ColB;
cards;
1 4
2 8
3 12
4 10
5 20
6 30
7 11
8 13
9 14
10 60
11 70
12 100
;
run;
data want;
set have;
runing_averae=sum(colB,lag(colB),lag2(colB),lag3(colB))/sum(colA,lag(colA),lag2(colA),lag3(colA));
run;
Please let us know if this worked for you.
Better post then output you want see.
data have; input ColA ColB; cards; 1 4 2 8 3 12 4 10 5 20 6 30 7 11 8 13 9 14 10 60 11 70 12 100 ; run; data want; merge have have(keep=colb rename=(colb=_colb) firstobs=5); laga=lag(cola);laga2=lag2(cola);laga3=lag3(cola);laga4=lag4(cola); lagb=lag(_colb);lagb2=lag2(_colb);lagb3=lag3(_colb);lagb4=lag4(_colb); if _n_>4 then want=sum(lagb,lagb2,lagb3,lagb4)/sum(laga,laga2,laga3,laga4); run;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.