BookmarkSubscribeRSS Feed
SachinRuk
Calcite | Level 5
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
NickR
Quartz | Level 8
data one;
do col1 = 1 to 15;
output;
end;
run;

data two;
set one;
col2=sum(col1,lag(col1),lag2(col1));
run;
SachinRuk
Calcite | Level 5
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
Cynthia_sas
SAS Super FREQ
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
SachinRuk
Calcite | Level 5
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
Cynthia_sas
SAS Super FREQ
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Vasile01
Fluorite | Level 6
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
DBailey
Lapis Lazuli | Level 10
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;
polingjw
Quartz | Level 8
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]
Ksharp
Super User
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

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1904 views
  • 0 likes
  • 8 in conversation