Picking up Every Second Last Observation

[ChrisNZ]

@Tom 

If the assumptions about the data change, the code needs to adapt to the new conditions.

The hierarchy of the run times remains the same.   POINT=   >   READ-AHEAD   >   DOW+BY

data TEMP3;
  set TEMP1 nobs=NOBS;
  by I T; 
  if ^LASTOBS then set TEMP1(firstobs=4 keep=I rename=(I=I1)) end=LASTOBS;
  by I1 ;
  if I ne I1 then KEEP=1;
  if _N_ > 3 then if first.I1 or lag1(first.I1) or lag2(first.I1) then KEEP=1;
  if _N_ > NOBS-3  then KEEP=1;                        if keep;
run;

Comparison of WORK.TEMP2 with WORK.TEMP3

NOTE: No unequal values were found. All values compared equal.

[ChrisNZ]

Note that an explicit DOW has a speed advantage over the implicit data step loop.

The first step is faster than the second one just because we don't use the implicit loop logic.

data TEMP3a;
  do N=1 by 1 until (LASTOBS);
    set TEMP1 nobs=NOBS end=LASTOBS;
    if ^LASTOBS1 then set TEMP1(firstobs=4 keep=I rename=(I=I1)) end=LASTOBS1;
    by I1 ;       
    if I ne I1 then KEEP=1;
    if N > 3 then if first.I1 or lag1(first.I1) or lag2(first.I1) then KEEP=1;
    if N > NOBS-3  then KEEP=1;                        
    if KEEP then output;           
    KEEP=0; 
  end;
run;
 
data TEMP3b;
    set TEMP1 nobs=NOBS ;
    if ^LASTOBS1 then set TEMP1(firstobs=4 keep=I rename=(I=I1)) end=LASTOBS1;
    by I1 ;                         
    if I ne I1 then KEEP=1;
    if _N_ > 3 then if first.I1 or lag1(first.I1) or lag2(first.I1) then KEEP=1;
    if _N_ > NOBS-3  then KEEP=1;                  
    if KEEP ;
run;

Your DOW step is faster than mine though (and arguably more legible), so better for the question at hand.

[Tom]

@ChrisNZ  I couldn't figure out the logic of why your step should work.

But I think you are essentially just doing this logic.

data temp3;
  set temp1;
  if not eof then set temp1(in=in1 firstobs=4 keep=I rename=(I=I1)) end=eof;
  else in1=0;
  if (I ne I1) or not in1;
run;

Or put another way.

data temp3;
  set temp1;
  if eof then output;
  else set temp1(firstobs=4 keep=I rename=(I=I1)) end=eof;
  if (I ne I1) then output ;
run;

[AndrewHowell]

Are I & T fixed at (say) 500 & 10, respectively?

If so, then just filter the top 3 values of T (8, 9, 10)?

data temp1;
	set temp;
	where t in (8,9,10);
run;

Or if this is oversimplifying, then try (something like) this:

data temp1;
	set temp;
	by i t;
	OnePrev=lag1(y); TwoPrev=lag2(y);
	if last.i then output;
run;

[mkeintz]

I'd be interested in knowing whether this simpler program is as fast:

data want;
  if end_of_temp=0 then set temp (keep=i t) end=end_of_temp;
  by i t ;
  if _n_>=3 then set temp;
  if last.i or lag(last.i) or lag2(last.i);
run;

The "if end_of_temp=0" condition prevents the data step from prematurely stopping, so that the "if _n_>=3 then set ...." statement can read the entire data set.

This program will include by groups with less than 3 observations.  If you want to exclude such groups, then modify the subsetting if to

  if (last.i or lag(last.i) or lag2(last.i)) and lag2(i)=i;

[ChrisNZ]

>But I think you are essentially just doing this logic.

@Tom Yes, and your code is more legible, again!

@mkeintz The speed is the same

data TEMP1;
  do I=1 to 5e6;
    do T=1 to ceil(ranuni(1)*10);
      Y=rand("normal");
      output;
    end;
  end;
run;

%* POINT=      40 s;
data TEMP2(compress=no);
  set TEMP1;
  by I ;
  if first.I then START = _N_;
  retain START;
  if last.I then do POINT = max(START, _N_ - 2) to _N_;
    set TEMP1 point=POINT;
    output;
  end;
  drop START;
run;

%* DOW + BY      13 s;
data TEMP3(compress=no);
  do _N=1 by 1 until(last.I);
    set TEMP1;
    by I;
  end;
  do _N2=1 to _N;
    set TEMP1;
    if _N2 >= _N-2 then output;
  end;
  drop _N _N2;
run;

%* Read-ahead + BY     18 s ;
data TEMP4(compress=no);
  set TEMP1 nobs=NOBS;
  by I T; 
  if ^LASTOBS then set TEMP1(firstobs=4 keep=I rename=(I=I1)) end=LASTOBS;
  by I1 ;
  if I ne I1 then KEEP=1;
  if _N_ > 3 then if first.I1 or lag1(first.I1) or lag2(first.I1) then KEEP=1;
  if _N_ > NOBS-3  then KEEP=1;                        if keep;
run ;
            
%* Read-ahead    13s ;
data TEMP5(compress=no);
  set TEMP1;
  if LASTOBS then output;
  else set TEMP1(firstobs=4 keep=I rename=(I=I1)) end=LASTOBS;
  if (I ne I1) then output ;
run;
     

[tatoknight]

Hi all,

Just with the code 

POINT = max(START, _N_ - 2) to _N_;

It works completely, but why? What is the purpose of the max function and how does it prevent errors? 

Thanks!

[PGStats]

START contains the position of the first record from the current group. If the group has less than 3 records, the max function prevents the loop from reading records from the previous group.