DATA Step, Macro, Functions and more

Adding the last 3 rows in a table

Reply
Contributor
Posts: 47

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
Frequent Contributor
Posts: 81

Re: Adding the last 3 rows in a table

Posted in reply to SachinRuk
data one;
do col1 = 1 to 15;
output;
end;
run;

data two;
set one;
col2=sum(col1,lag(col1),lag2(col1));
run;
Contributor
Posts: 47

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
Posts: 8,868

Re: Adding the last 3 rows in a table

Posted in reply to SachinRuk
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
Contributor
Posts: 47

Re: Adding the last 3 rows in a table

Posted in reply to Cynthia_sas
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
Posts: 8,868

Re: Adding the last 3 rows in a table

Posted in reply to SachinRuk
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
Super Contributor
Super Contributor
Posts: 3,174

Re: Adding the last 3 rows in a table

Posted in reply to Cynthia_sas
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.
Occasional Contributor
Posts: 14

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

Posted in reply to SachinRuk
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
Super Contributor
Posts: 578

Re: Adding the last 3 rows in a table

Posted in reply to SachinRuk
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;
Regular Contributor
Posts: 171

Re: Adding the last 3 rows in a table

Posted in reply to SachinRuk
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
Posts: 10,044

Re: Adding the last 3 rows in a table

Posted in reply to SachinRuk
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
Ask a Question
Discussion stats
  • 10 replies
  • 301 views
  • 0 likes
  • 8 in conversation