BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Marwa_Se
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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

 

Have a nice journey

Ksharp
Super User
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
Obsidian | Level 7

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

hashman
Ammonite | Level 13

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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