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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.