How do I replace missing value with previous value, but leave duplicate IDs with missings alone?

[atiedt]

Hello,

I have a dataset in long format, with multiple date per ID, which looks like this;

ID   Cycle_#  Date

1     1           1/01/2016

1     2           2/02/2016

1     3           .

2     1           .

2     2           .

2     3           .

3     1          3/04/2016

3     2          4/12/2016

3     3          5/06/2016

3     4          .

4     1          .

5     1         8/16/2015

5     2         9/01/2016

I used the code below to replace missing dates with previous non-missing values. However, for IDs that do not have any dates (e.g., ID 2 above), I'd like to leave the dates as missing. How can I adjust the code below to prevent IDs with all missing takes from being replaced with the previous date? I'm using SAS EG 7.1.

data t2 (drop=fill new);

  retain fill;

   do until (date ne . or last.cycle_#);

     set t;

     by id cycle_#;

   end;

  if date ne . then fill = date;

  do until (new ne . or last.cycle_#);

    set t;

    by intkey cycle_count;

          new = date;

    if new = . then date = fill;

    output;

  end;

run;

[Astounding]

It's possible there's a simpler way:

data want;

set have;

by id;

if first.id or date > . then fill=date;

retain fill;

drop date;

rename fill=date;

run;

[atiedt]

Thanks for your suggestion. The code works well to leave the IDs with all missing dates alone, but unfortunately, doesn't replace IDs with more than one trailing missing date.

[Astounding · Accepted solution]

It should work.  The only case it doesn't cover is when your first date for an ID is missing.  It doesn't look foward to find later dates as a replacement.

Do you have a small example ... a few lines of data that didn't come out the way you wanted?

By any chance, did you use ">=" instead of ">" as the comparison?

[atiedt]

Yup, small typo. Works fine, thanks so much!

[JohnSAScom]

Here's a version that doesn't rely on dropping or re-naming data set variables: 

A couple of notes:

1. Missing values for character variables are represented by a blank.
2. Data set variable names can only contain letters, numbers and underscores.

This is one solution:

data question;

                ID = 1;  

                Cycle = 1;

                Date = '1/01/2016';

                output;

                ID = 1;  

                Cycle = 2;

                Date = '2/02/2016';

                output;

                ID = 1;  

                Cycle = 3;

                Date = '';

                output;

                ID = 2;  

                Cycle = 1;

                Date = '';

                output;

                ID = 2;  

                Cycle = 2;

                Date = '';

                output;

                ID = 2;  

                Cycle = 3;

                Date = '';

                output;

                ID = 3;  

                Cycle = 1;

                Date = '3/04/2016';

                output;

                ID = 3;  

                Cycle = 2;

                Date = '4/12/2016';

                output;

                ID = 3;  

                Cycle = 3;

                Date = '5/06/2016';

                output;

                ID = 3;  

                Cycle = 4;

                Date = '';

                output;

                ID = 4;  

                Cycle = 1;

                Date = '';

                output;

                ID = 5;  

                Cycle = 1;

                Date = '8/16/2015';

                output;

                ID = 5;  

                Cycle = 2;

                Date = '9/01/2016';

                output;

run;

%macro a;

                proc sort data=question;

                   by ID Cycle;

                run;

                proc sql noprint;

                   select count(distinct(ID)) into:distinct_IDs from question;

                quit;

                %do i = 1 %to &distinct_IDs.;

                                proc sql noprint;

                                   create table unique_id as

                                   select * from question where ID = &i.;

                                   select count(*) into:TOTAL from unique_id;

                                   select count(*) into:MISSING from unique_id where Date = '';

                                quit;

        %if &MISSING. gt 0 and &TOTAL. ne &MISSING. %then %do;

                                                data _null_;

                           set unique_id;

                                                   if Date ne '' then call symput('last_date', Date);

                                                run;

                                                proc sql noprint;

                                                   update question set Date = "&last_date." where Date = '' and ID = &i.;

                                                quit;

                                %end;

                %end;

%mend a;

%a;

Thank you,

John