```Date: Sat, 22 Mar 2008 20:21:15 +0000 Reply-To: Paul Dorfman Sender: "SAS(r) Discussion" From: Paul Dorfman Organization: PDC Subject: Re: Can the lag function be used to populate prior obs with successive observation values? Comments: To: Jerry L Diebal 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 : -------------- > 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; ```

Back to: Top of message | Previous page | Main SAS-L page