topic Re: Replace the missing values in SAS Programming

Replace the missing values

Marwa_Se — Thu, 02 Aug 2018 07:43:25 GMT

Hello,

I have a data set as follows:

username values

AAA .

AAA 5

AAA .

BBB 1

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

BBB 1

CCC .

DDD 5

Do u have any suggestion guys ?

Thank you in advance for ur help

Re: Replace the missing values

RW9 — Thu, 02 Aug 2018 07:54:34 GMT

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;

Re: Replace the missing values

Marwa_Se — Thu, 02 Aug 2018 07:58:05 GMT

Thank u so much, this is exactly what i wanted

Have a nice journey

Re: Replace the missing values

Ksharp — Thu, 02 Aug 2018 12:59:03 GMT

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;

Re: Replace the missing values

Marwa_Se — Thu, 02 Aug 2018 13:08:38 GMT

This solution works good also, thank you for your help

Re: Replace the missing values

hashman — Thu, 02 Aug 2018 16:23:38 GMT

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