sas complete missing values in a group

damian_HMRC — Fri, 23 Jun 2017 14:45:09 GMT

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;

Re: sas complete missing values in a group

ballardw — Fri, 23 Jun 2017 15:02:01 GMT

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;

topic Re: sas complete missing values in a group in SAS Programming

sas complete missing values in a group

Re: sas complete missing values in a group