DATA Step, Macro, Functions and more

Replace the missing values

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Replace the missing values

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  Woman Happy


Accepted Solutions
Solution
2 weeks ago
Super User
Super User
Posts: 9,799

Re: Replace the missing values

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;

View solution in original post


All Replies
Solution
2 weeks ago
Super User
Super User
Posts: 9,799

Re: Replace the missing values

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;
Contributor
Posts: 20

Re: Replace the missing values

Thank u so much, this is exactly what i wanted Woman Happy

 

Have a nice journey

Super User
Posts: 10,846

Re: Replace the missing values

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;
Contributor
Posts: 20

Re: Replace the missing values

This solution works  good also, thank you for your help Woman Happy

Frequent Contributor
Posts: 112

Re: Replace the missing values

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 99 views
  • 8 likes
  • 4 in conversation