topic Re: Picking up Every Second Last Observation in SAS Programming

Picking up Every Second Last Observation

Junyong — Sun, 07 Apr 2019 23:45:33 GMT

Suppose there is a panel data set indexed by "i" and "t," respectively.

data temp;
call streaminit(1);
do i=1 to 500;
do t=1 to 10;
y=rand("normal");
output;
end;
end;
run;

Assume that one picks up every first, second, and third observations for all "i" using FIRST and LAG.

data temp1;
set temp;
by i t;
if first.i or lag1(first.i) or lag2(first.i);
run;

Can one pick up every last, second last, and third last observations in a similar way? Though LAST is available for all the last observations, the second and third last observations are not easy.

data temp2;
set temp;
by i t;
if last.i;
run;

Is PROC SORT plus DESCENDING the only option here?

P.S. Though LAG7 and LAG8 are possible for this case, please assume a general unbalanced panel data set.

Re: Picking up Every Second Last Observation

Astounding — Mon, 08 Apr 2019 02:07:48 GMT

Make two passes through the data. For example:

data want;
tot_count=0;
do until (last.id) ;
set have;
by id;
tot_count + 1;
end;
K=0;
do until (last.id) ;
set have;
by id;
k + 1;
if k in (1, 2, 3, tot_count, tot_count-1, tot_count-2) then output;
end;
drop k tot_count;
run;

Re: Picking up Every Second Last Observation

ChrisNZ — Mon, 08 Apr 2019 03:15:31 GMT

Reading ahead works to:

data TEMP2;
  set TEMP nobs=NOBS;
  by I T; 
  if ^LASTOBS then set TEMP(firstobs=4 keep=I rename=(I=I1)) end=LASTOBS;
  by I1 ;
  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;
run;

Reading 3 observations ahead allows to mark the first 3 of the shifted observations. These match with the last 3 of the original observations .

Re: Picking up Every Second Last Observation

PGStats — Mon, 08 Apr 2019 04:11:59 GMT

Random access keeps I/O to a minimum:

proc sort data=sashelp.class out=have; by sex name; run;

data last3;
set have; by sex;
if first.sex then start = _n_;
retain start;
if last.sex then do point = max(start, _n_ - 2) to _n_;
    set have point=point;
    output;
    end;
drop start;
run;

EDIT Added retain statement as suggested by @Tom .

Re: Picking up Every Second Last Observation

ChrisNZ — Mon, 08 Apr 2019 03:41:16 GMT

@PGStats

POINT= is extremely slow. It is best avoided unless the number of records retrieved is low.

Re: Picking up Every Second Last Observation

PGStats — Mon, 08 Apr 2019 03:52:19 GMT

That's true in general @ChrisNZ , but here, the records accessed at random have just been read sequentially, and there are only three of them per by-group. So, random access should be at least considered.

Re: Picking up Every Second Last Observation

ChrisNZ — Mon, 08 Apr 2019 03:59:59 GMT

> the records accessed at random have just been read sequentially, and there are only three of them per by-group. So, random access should be at least considered.

@PGStats Fair enough.

And if groups can have fewer than 3 observations, additional logic is required.

Re: Picking up Every Second Last Observation

AndrewHowell — Mon, 08 Apr 2019 04:02:26 GMT

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;

Re: Picking up Every Second Last Observation

PGStats — Mon, 08 Apr 2019 04:02:37 GMT

Variable START in my code accounts for that possibility.

Re: Picking up Every Second Last Observation

Tom — Mon, 08 Apr 2019 04:26:57 GMT

POINT= seems to work great for this problem.

But a double DOW seems to be a little faster.

Using extra SET statements to look ahead seems to be slowest.

For 5,000,000 observations:

320   data temp2;
321     set temp1;
322     by I ;
323     if first.i then start = _n_;
324     retain start;
325     if last.i then do point = max(start, _n_ - 2) to _n_;
326       set temp1 point=point;
327       output;
328     end;
329     drop start;
330   run;

NOTE: There were 5000000 observations read from the data set WORK.TEMP1.
NOTE: The data set WORK.TEMP2 has 1500000 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.57 seconds
      cpu time            0.60 seconds


331
332   data TEMP3;
333   do _n=1 by 1 until(last.i);
334     set temp1;
335     by i;
336   end;
337   do _n2=1 to _n;
338     set temp1;
339     if _n2 >= _n-2 then output;
340   end;
341   drop _n _n2;
342   run;

NOTE: There were 5000000 observations read from the data set WORK.TEMP1.
NOTE: There were 5000000 observations read from the data set WORK.TEMP1.
NOTE: The data set WORK.TEMP3 has 1500000 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.48 seconds
      cpu time            0.48 seconds


343
344
345   data TEMP4;
346     set TEMP1 end=eof1 ;
347     by I;
348     if not eof1 then set temp1(firstobs=2 keep=I rename=(i=i2)) end=eof2;
349     by i2 ;
350     if not eof2 then set temp1(firstobs=3 keep=I rename=(i=i3)) end=eof3;
351     by i3 ;
352     if last.i or (last.i2 and i=i2) or (last.i3 and i=i3);
353   run;

NOTE: There were 5000000 observations read from the data set WORK.TEMP1.
NOTE: There were 4999999 observations read from the data set WORK.TEMP1.
NOTE: There were 4999998 observations read from the data set WORK.TEMP1.
NOTE: The data set WORK.TEMP4 has 1500000 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           1.20 seconds
      cpu time            1.17 seconds

Re: Picking up Every Second Last Observation

ChrisNZ — Mon, 08 Apr 2019 04:32:52 GMT

@Tom For speed, my tests confirm your findings: DOW > read-ahead > POINT=

Having only one BY statement is probably the reason. BY statements really hinder speed.

Re: Picking up Every Second Last Observation

ChrisNZ — Mon, 08 Apr 2019 04:52:11 GMT

@Tom A DOW with no BY seems ever so slightly faster

data temp1;
call streaminit(1);
do i=1 to 5e5;
do t=1 to 10;
y=rand("normal");
output;
end;
end;
run;

%* POINT=   ==> 6.99 real/6.3 CPU seconds;
data temp2;
  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;

%* read ahead   ==> 4.74 real/3.82 CPU seconds;
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 _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;

%* DOW + BY   ==> 3.12 real/2.62 CPU seconds;
data TEMP4;
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;

%* DOW no BY   ==> 3.09 real/2.45 CPU seconds;
data TEMP5;
do _n=1 by 1 until(_N >1 and I ne lag(I));
  set temp1;     
end;        
do _n2=1 to _n;
  set temp1 nobs=NOBS;
  if _N > _n2 >= _n-3 | _N2>=NOBS-3 then output;
end;
drop _n _n2;
run;

Re: Picking up Every Second Last Observation

ChrisNZ — Mon, 08 Apr 2019 21:36:00 GMT

@Tom You use three SET BY, hence the poor speed, since BY adds so much overhead. They are not needed.

Re: Picking up Every Second Last Observation

Tom — Mon, 08 Apr 2019 21:39:18 GMT

How can you find the 3rd and 2nd to last without the BY statements?

Re: Picking up Every Second Last Observation

ChrisNZ — Mon, 08 Apr 2019 21:58:17 GMT

@Tom See my first post

Here is my benchmark:

data temp1;
call streaminit(1);
do i=1 to 5e5;
do t=1 to 10;
y=rand("normal");
output;
end;
end;
run;

%* POINT=   ==> 6.99 real/6.3 CPU seconds;
data temp2;
  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;

%* read ahead   ==> 4.74 real/3.82 CPU seconds;
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 _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;

%* DOW + BY   ==> 3.12 real/2.62 CPU seconds;
data TEMP4;
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;

%* DOW no BY   ==> 3.09 real/2.45 CPU seconds;
data TEMP5;
do _n=1 by 1 until(_N >1 and I ne lag(I));
  set temp1;     
end;        
do _n2=1 to _n;
  set temp1 nobs=NOBS;
  if _N > _n2 >= _n-3 | _N2>=NOBS-3 then output;
end;
drop _n _n2;
run;

Re: Picking up Every Second Last Observation

Tom — Tue, 09 Apr 2019 00:02:09 GMT

@ChrisNZ But it doesn't get the same output dataset.

data temp1;
call streaminit(1);
do i=1 to 5000;
do t=1 to ceil(i/10);
y=rand("normal");
output;
end;
end;
run;

data TEMP2;
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;

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 _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;

The COMPARE Procedure                                                                                      
Comparison of WORK.TEMP2 with WORK.TEMP3                                                                   
(Method=EXACT)                                                                                             
                                                                                                           
Observation Summary                                                                                        
                                                                                                           
Observation      Base  Compare  ID                                                                         
                                                                                                           
First Obs           1        .  i=1 t=1                                                                    
First Match         4        1  i=4 t=1                                                                    
Last  Obs       14970    14967  i=5000 t=500                                                               
                                                                                                           
Number of Observations in Common: 14967.                                                                   
Number of Observations in WORK.TEMP2 but not in WORK.TEMP3: 3.                                             
Total Number of Observations Read from WORK.TEMP2: 14970.                                                  
Total Number of Observations Read from WORK.TEMP3: 14967.                                                  
                                                                                                           
Number of Observations with Some Compared Variables Unequal: 0.                                            
Number of Observations with All Compared Variables Equal: 14967.                                           
                                                                                                           
NOTE: No unequal values were found. All values compared are exactly equal.

Re: Picking up Every Second Last Observation

ChrisNZ — Tue, 09 Apr 2019 03:58:30 GMT

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

Re: Picking up Every Second Last Observation

ChrisNZ — Tue, 09 Apr 2019 04:03:00 GMT

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.

Re: Picking up Every Second Last Observation

mkeintz — Tue, 09 Apr 2019 05:02:06 GMT

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;

Re: Picking up Every Second Last Observation

Tom — Tue, 09 Apr 2019 05:19:05 GMT

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