Calcite | Level 5

## Adding the last 3 rows in a table

Hi all,

I want to add the last 3 rows for every row for a particular table.

Suppose my table looked like this:

col1
1
2
3
4
5
6

I want the resulting table to be (disregarding first two rows):

col1
6
9
12
15

Thanks
Sachin
10 REPLIES 10
Quartz | Level 8

## Re: Adding the last 3 rows in a table

data one;
do col1 = 1 to 15;
output;
end;
run;

data two;
set one;
col2=sum(col1,lag(col1),lag2(col1));
run;
Calcite | Level 5

## Re: Adding the last 3 rows in a table

is it possible to do it without the lag function? Only because as an example I said 3 but its most likely to be 12 and may vary.

For example can we do this using _n_ and a do loop?
/*for each _n_ (row)*/
/*loop 12 times with previous 12 rows and add up*/

Thanks
Sachin
SAS Super FREQ

## Re: Adding the last 3 rows in a table

Hi:
Based on the example data you posted, I don't understand how you will know whether you need to add 3 observation's values or whether you need to add 12 observation's values. Perhaps you have oversimplified your example data and there is more to explain about what you need to do???

cynthia
Calcite | Level 5

## Re: Adding the last 3 rows in a table

Hi Cynthia,

Suppose I want to add the last three variables (forget what I said about trying to add variable number of rows), is there another way to do it without using a lag operator?

Thanks,
Sachin
SAS Super FREQ

## Re: Adding the last 3 rows in a table

Hi:
I don't feel there's enough information to even guess at an answer. For example...you have a dataset with ONLY 1 variable or column (COL1) and some number of rows or observations?? There are no identifying variables?? It's also sort of hard to "forget" that you mentioned the possibility of a variable number of rows to add up (maybe 3, maybe 12). You do not want to use LAG, but why not?? Are you rejecting LAG for some reason??

Possibly transposing the data and then using an ARRAY might work. Really, without more information, it's like putting a 500 piece puzzle together with someone who's handing me only 3 puzzle pieces at a time.

Perhaps other folks will have some better ideas.

cynthia
Lapis Lazuli | Level 10

## Re: Adding the last 3 rows in a table

Could this be an oversimplified approach - for each set of 3 observations, only output the third observation (this example uses the SAS implicit OUTPUT approach):

data x;
do i=1 to 3;
set sashelp.class;
end;
run;

Scott Barry
SBBWorks, Inc.
Fluorite | Level 6

## Re: Adding the last 3 rows in a table/parameter

Hi,

You could use parameters and lags too:

%macro lagi(nlags=2);
%local i;
data sumlags(keep= sum);
set test ;
sum=col1;
%do i=1 %to &nlags;
x=lag&i(col1);
sum=sum+x;
%end;
if sum ne . then output;
run;
%mend lagi;
%lagi(nlags=5);

Warm regards,
Vasile
Lapis Lazuli | Level 10

## Re: Adding the last 3 rows in a table

This might work for an input file. Not sure about an input dataset or some of the syntax

data work.tst(keep=sumcol);
infile '/tst.txt';
retain sumcol;
sumcol=0
do while (i>0);
input #(-i) inputcol
sumcol = sumcol + inputcol;
I+ -1;
end;

run;
Quartz | Level 8

## Re: Adding the last 3 rows in a table

I’m not sure if this is ultimately what you want, but it works for the simplified data that you posted.

[pre]
%let rows_to_sum = 3;

data test;
input var;
datalines;
1
2
3
4
5
6
;
run;

data _null_;
call symputx("numvars", n);
if 0 then set test nobs=n;
run;
data result;
array vars{&numvars} _temporary_;
do i=1 to dim(vars);
set test;
vars{i}=var;
end;
do i=1 to &numvars - &rows_to_sum + 1;
result = 0;
do j=i to i+&rows_to_sum-1;
result+vars{j};
end;
output;
end;
keep result;
run;

proc print data=result noobs;
run;
[/pre]
Super User

## Re: Adding the last 3 rows in a table

Emmmmm.
Maybe You will like stack skill which has mentioned by Art.C before.

[pre]
%let rows = 3;

data test;
input var;
datalines;
1
2
3
4
5
6
;
run;

data want(keep=sum);
set test;
array stack{&rows} _temporary_;
i=mod(_n_,&rows)+1;
stack{i}=var;
sum=sum(of stack{*});
if _n_ ge &rows then output;
run;
[/pre]

Ksharp
Discussion stats
• 10 replies
• 1973 views
• 0 likes
• 8 in conversation