topic Re: Iterate through dataset with specific number of rows in SAS Programming

Iterate through dataset with specific number of rows

swamgan12 — Sun, 16 Aug 2020 20:53:57 GMT

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

Re: Iterate through dataset with specific number of rows

PaigeMiller — Sun, 16 Aug 2020 21:01:17 GMT

Iterate through the specific rows and do what?

What should the output of this process look like?

Re: Iterate through dataset with specific number of rows

Tom — Sun, 16 Aug 2020 21:19:58 GMT

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;

Re: Iterate through dataset with specific number of rows

PGStats — Mon, 17 Aug 2020 02:45:04 GMT

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:

Re: Iterate through dataset with specific number of rows

swamgan12 — Mon, 17 Aug 2020 08:32:04 GMT

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.

Re: Iterate through dataset with specific number of rows

andreas_lds — Mon, 17 Aug 2020 08:52:15 GMT

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.

Re: Iterate through dataset with specific number of rows

PaigeMiller — Mon, 17 Aug 2020 10:40:42 GMT

@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.

Re: Iterate through dataset with specific number of rows

Ksharp — Mon, 17 Aug 2020 12:03:03 GMT

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;