Iterate through dataset with specific number of rows

[swamgan12]

Hello,

     I'm trying to iterate through a dataset for a specific set of rows (say 5), so for the first observation when it is read it has to read through the next 4 rows. So when we read the second observation it has to loop through 3,4,5,6 rows and for third obs read through rows 4-7 & so on... When it comes to the bottom rows it should read what is left over. Like in row 10, it can read only 11  & 12. 

Is there a way to achieve this through retain statements or array?

the dataset might look like below:

DSet : A

ID    Date    Cost

1      8/1/2020       100

2      8/2/2020        230

3      8/3/2020        450

4      8/4/2020        660

5      8/5/2020        670

6      8/6/2020        230

7      8/7/2020        420

8      8/8/2020        110

9      8/9/2020        430

10    8/10/2020      140

11    8/11/2020      180

12    8/12/2020      340

[PaigeMiller]

Iterate through the specific rows and do what?

What should the output of this process look like?

[swamgan12]

Sorry should have been more clear... Once I read the 5 observations have to sum up the Cost columns.. I have like 3 Cost columns, Cost1, Cost2, Cost3. Need to sum all the 3 columns across 5 observations and see which exceeds.

[andreas_lds]

To avoid further tinkering, please post what you expect as result from the data you have posted. And take care that the data is as close as possible to your real-world-data.

[PaigeMiller]

---

@swamgan12 wrote:

Sorry should have been more clear... Once I read the 5 observations have to sum up the Cost columns.. I have like 3 Cost columns, Cost1, Cost2, Cost3. Need to sum all the 3 columns across 5 observations and see which exceeds.

---

But you don't show columns COST1, COST2 and COST3 in your example data set.

Please provide a clear explanation, which matches the data in your example, and please show the desired output.

[Tom]

What are you trying to do with those other row's values?

Should be simple with POINT= option.

data want;
   set have ;
   do p=_n_+1 to min(_n_+4,nobs);
      set have point=p nobs=nobs;
      * do somthing? ;
   end;
run;

[PGStats]

Or,

data b;
length lastRow 8;
set a;
do lastRow = max(5, _n_) to _n_ + 4;
    output;
    end;
run;

proc sort data=b; by lastRow date; run;

alternatively:

[Ksharp]

It is look-forward problem.

data a;
input ID    Date  : $20.  Cost;
cards;
1      8/1/2020       100
2      8/2/2020        230
3      8/3/2020        450
4      8/4/2020        660
5      8/5/2020        670
6      8/6/2020        230
7      8/7/2020        420
8      8/8/2020        110
9      8/9/2020        430
10    8/10/2020      140
11    8/11/2020      180
12    8/12/2020      340
;

data want;
 merge a(rename=(cost=cost1))
       a(keep=cost rename=(cost=cost2) firstobs=2)
	   a(keep=cost rename=(cost=cost3) firstobs=3)
       a(keep=cost rename=(cost=cost4) firstobs=4)
       a(keep=cost rename=(cost=cost5) firstobs=5);
sum=sum(of cost1-cost5);
run;