@Marwa_Se: @Ksharp's merge solution is terse and sweet, but it relies on the input data set's sorted order. @RW9's SQL solution is more universal since it doesn't rely on any order. Methought it'd be nice to augment these fine pieces of coding with a DATA step approach that doesn't need the input to be in order, either: data want (drop = _:) ;
if _n_ = 1 then do ;
dcl hash h (dataset:"have", multidata:"y") ;
h.definekey ("username") ;
h.definedata ("values") ;
h.definedone () ;
end ;
set have ;
if h.find() = 0 then do until (h.find_next() ne 0) ;
if not missing (values) then _v = values ;
end ;
values = _v ;
run ; Note that the DO loop following SET is coded in such a way that it will work in both 9.3 and 9.4. In 9.4 and up, it can be coded simpler: do while (h.do_over() = 0) ;
if not missing (values) then _v = values ;
end ; Finally, if the input is sorted, I'd personally prefer @Ksharp's merge. Just for the record, this kind of processing nicely yields to the intrinsic structure of the double DoW loop: data want (drop = _:) ;
do _n_ = 1 by 1 until (last.username) ;
set have ;
by username ;
if not missing (values) then _v = values ;
end ;
do _n_ = 1 to _n_ ;
set have ;
values = _v ;
output ;
end ;
run ; Paul D.
... View more
