sas complete missing values in a group

[damian_HMRC]

I have nearly cracked it using suggestions from sililar posts but my problem appears subtly different and its not working correctly.

What I am trying to do is copy a value for one customer with different sets where some values are missing.

Please note we do not have a name for customer 333 and my code is bringing down customer name from 222 which is incorrect.

eg: dataset                                ouput given                         output required

ID       set     name               ID       set     name                 ID       set     name

111      A       Fred                 111      A       Fred                  111      A       Fred

111      B       Fred                 111      B       Fred                  111      B       Fred

111      C                                111     C       Fred                  111      C       Fred

222      A       Jane                222      A       Jane                  222      A       Jane

222      D                              222      D       Jane                  222      D      Jane

333      B                              333      B        Jane                 333      B

333      C                             333      C         Jane                333      C

444      A       Daphne          444      A       Daphne              444      A       Daphne

-------code--------

data rlt_fill2;
set rlt_fill;
by id;

do n=_n_ until (last.id);
if missing(name) then do;
do until (not missing(CRM));
n=n-1;

set rlt_fill (keep=crm) point=n;
end;
end;
end;
run;

[ballardw · Accepted solution]

Here is one way. This requires the data set to be sorted by ID. The first. (and a corresponding Last. ) is an automatic variable that indicates if the current record represents the first (or last) record of a BY group. So we can process the first record of an ID differently and not assign the name from the previous ID.

data have;
   infile datalines truncover;
   input ID       set  $   name   $;
datalines;
111      A       Fred   
111      B       Fred   
111      C              
222      A       Jane   
222      D              
333      B              
333      C              
444      A       Daphne 
;
run;

data want;
   set have;
   by id;
   length lastname $ 8; /* should match length of NAME variable*/
   retain lastname ;
   if first.id then call missing(lastname);
   if not missing(name) then lastname=name;
   if missing(name) then name=lastname;
   drop lastname;
run;