Date:         Sat, 22 Mar 2008 20:21:15 +0000
Reply-To:     Paul Dorfman <sashole@BELLSOUTH.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Paul Dorfman <sashole@BELLSOUTH.NET>
Organization: PDC
Subject:      Re: Can the lag function be used to populate prior obs with
              successive observation values?
Comments: To: Jerry L Diebal <jdiebal@GMAIL.COM>

Jerry,

The answer is "yes", even though it appears to be somewhat impractical to do it this way, and if you do not mind reading the data twice backwards. Consider:

data have ;
   input wc $ order $ emp $  LABOR op STD SHIFT ;
cards ;
wc1 order1 emp1  20 10 1 1
wc2 order1 .     .  20 1 .
wc2 order1 emp2  .  30 1 2
wc3 order1 emp1  30 40 1 1
wc3 order1 .     .  50 1 .
wc3 order1 .     .  60 1 .
wc4 order1 emp3  40 70 1 2
run;

data reverse (drop = _:) ;
   do _n_ = n by -1 to 1 ;
      set have point = _n_ nobs = n ;
      if not missing (shift) then do _x = 1, 2 ;
        _shift = lag (shift) ;
      end ;
      else shift = _shift  ;
      if not missing (emp  ) then do _x = 1, 2 ;
        _emp   = lag (emp  ) ;
      end ;
      else emp = _emp    ;
      output ;
   end ;
   stop ;
run ;

data need ;
   do _n_ = n by -1 to 1 ;
      set reverse point = _n_ nobs = n ;
      output ;
   end ;
   stop ;
run ;

Note that the DO _X = 1, 2 trick is needed to make sure that the LAG's 1-element
queue ejects a non-missing value. So if it is already decided to do the double-reverse reading exercise, it makes more sense to eschew LAG altogether and shift the burden of populating the missing stuff on the second step since in the latter, needed non-missing values *precede* the missing values to be filled:

data reverse (drop = _:) ;
   do _n_ = n by -1 to 1 ;
      set have point = _n_ nobs = n ;
      output ;
   end ;
   stop ;
run ;

data need (drop = _:)  ;
   do _n_ = n by -1 to 1 ;
      set reverse point = _n_ nobs = n ;
      if not missing (emp  ) then _emp   =  emp   ;
      else                         emp   = _emp   ;
      if not missing (shift) then _shift =  shift ;
      else                         shift = _shift ;
      output ;
   end ;
   stop ;
run ;

Yet on the other hand, you can accomplish the same by reading the file serially, from time to time looking forward. Every time a non-hole is encountered, use direct access to read forward until the next non-hole and use it to fill all the holes in between:

data need (drop = _:)  ;
   set have nobs = n ;
   if not missing (shift) then do p = _n_ + 1 to n ;
      if p <= n then set have (keep = shift rename = (shift=_shift)) point = p ;
      if not missing (_shift) then leave ;
   end ;
   else shift = _shift ;
   if not missing (emp) then do p = _n_ + 1 to n ;
      if p <= n then set have (keep = emp rename = (emp=_emp)) point = p ;
      if not missing (_emp) then leave ;
   end ;
   else emp = _emp ;
run ;

Of course, if the holes for SHIFT and EMP are always in sync, only one look-ahead is necessary (the same concept pertaining to all previous code pieces):

data need (drop = _:)  ;
   set have nobs = n ;
   if not missing (shift) then do p = _n_ + 1 to n until (not missing (_shift)) ;
      if p <= n then set have (keep = shift emp rename = (shift=_shift emp=_emp)) point = p ;
   end ;
   else do ;
      shift = _shift ;
      emp   = _emp   ;
   end ;
run ;

But then, if SHIFT and EMP *are* always hole-synced, you can get away with 2 serial reads only by first reading a group whose break-event is a non-missing SHIFT/EMP values, then re-reading the group and using the value[s] to plug the holes :

data need (drop = _:)  ;
   do _n_ = 1 by 1 until (not missing (_shift)) ;
      set have (keep = shift emp rename = (shift=_shift emp=_emp)) ;
   end ;
   do _n_ = 1 to _n_ ;
      set have ;
      if missing (shift) then do ;
         shift = _shift ;
         emp   = _emp   ;
      end ;
      output ;
   end ;
run ;

Kind regards
------------
Paul Dorfman
Jax, FL
------------

  -------------- Original message from Jerry L Diebal <jdiebal@GMAIL.COM>: --------------


> I have the data below where the last occurrence of the emp and shift needs
> to populate the previous observation that are blank. Can the lag function be
> used for this? Thanks in advance for any suggestions.
>
> data have;
>        input wc $ order $ emp $  LABOR op STD SHIFT;
> datalines;
> wc1 order1 emp1  20 10 1 1
> wc2 order1 .     .  20 1 .
> wc2 order1 emp2  .  30 1 2
> wc3 order1 emp1  30 40 1 1
> wc3 order1 .     .  50 1 .
> wc3 order1 .     .  60 1 .
> wc4 order1 emp3  40 70 1 2
> ;
> run;
>
>
> data want;
>        input wc $ order $ emp $  LABOR op STD SHIFT;
> datalines;
> wc1 order1 emp1  20 10 1 1
> wc2 order1 emp2  .  20 1 2
> wc2 order1 emp2  .  30 1 2
> wc3 order1 emp1  30 40 1 1
> wc3 order1 emp3  .  50 1 2
> wc3 order1 emp3  .  60 1 2
> wc4 order1 emp3  40 70 1 2
> ;
> run;