topic Adding the last 3 rows in a table in SAS Programming

Adding the last 3 rows in a table

SachinRuk — Tue, 15 Feb 2011 01:05:27 GMT

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

Re: Adding the last 3 rows in a table

NickR — Tue, 15 Feb 2011 01:53:59 GMT

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

data two;
set one;
col2=sum(col1,lag(col1),lag2(col1));
run;

Re: Adding the last 3 rows in a table

SachinRuk — Tue, 15 Feb 2011 02:51:34 GMT

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

Re: Adding the last 3 rows in a table

Cynthia_sas — Tue, 15 Feb 2011 04:06:59 GMT

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

Re: Adding the last 3 rows in a table

SachinRuk — Tue, 15 Feb 2011 05:42:46 GMT

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

Re: Adding the last 3 rows in a table

Cynthia_sas — Tue, 15 Feb 2011 06:21:22 GMT

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

Re: Adding the last 3 rows in a table

sbb — Tue, 15 Feb 2011 11:49:58 GMT

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.

Re: Adding the last 3 rows in a table

DBailey — Tue, 15 Feb 2011 12:46:46 GMT

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';
rows_to_add=3;
i=rows_to_add;
retain sumcol;
if _N_ ge rows_to_add;
i=rows_to_add;
sumcol=0
do while (i>0);
input #(-i) inputcol
sumcol = sumcol + inputcol;
I+ -1;
end;

run;

Re: Adding the last 3 rows in a table

polingjw — Tue, 15 Feb 2011 15:29:38 GMT

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]

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

Vasile01 — Tue, 15 Feb 2011 16:21:59 GMT

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

Re: Adding the last 3 rows in a table

Ksharp — Thu, 24 Feb 2011 07:21:19 GMT

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