Replace the missing values

[Marwa_Se]

Hello,

I have a data set as follows:

username                 values 

AAA                           .

AAA                           .

AAA                           5

AAA                           .

AAA                           .

BBB                           1

BBB                            .

BBB                            .

CCC                           .

DDD                           .

DDD                           5

i would like to replace the missing values for each username with its non null one, in other way i would like to obtain something like this:

Usernames            values

AAA                           5

AAA                           5

AAA                           5

AAA                            5

AAA                           5

BBB                            1

BBB                            1

BBB                            1

CCC                            .

DDD                           5

DDD                           5

Do u have any suggestion guys ?

Thank you in advance for ur help 

[RW9 · Accepted solution]

What happens if more than one row for username has a number?  Also, please provide test data in the form of a datastep, as such this is untested:

proc sql;
  create table want as
  select  a.username,
          coalesce(a.values,b.values) as values
  from    have a
  left join (select distinct username,values from have where values ne .) b
  on      a.username=b.username;
quit;

[Marwa_Se]

Thank u so much, this is exactly what i wanted

Have a nice journey

[Ksharp]

data have;
input username $         values ;
cards;
AAA                           .
AAA                           .
AAA                           5
AAA                           .
AAA                           .
BBB                           1
BBB                            .
BBB                            .
CCC                           .
DDD                           .
DDD                           5
;
data want;
 merge have(drop=values) have(where=(values is not missing));
 by username;
run;

[Marwa_Se]

This solution works  good also, thank you for your help

[hashman]

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