Fill row with last non blank entry by group

[GS2]

Using SAS 9.4

I have data that looks like this

ID    Date

1    .

1    .

1   .

1   01/21/2020

I would like to fill the column so that it looks like this:

ID    Date

1    01/21/2020

1    01/21/2020

1   01/21/2020

1   01/21/2020

I have tried this code

data test_set;
set have;
by record_id;
retain _phone_date;
if not missing (phone_date) then _phone_date = phone_date;
else phone_date = _phone_date;
drop _phone_date;
run;

However this retains the non blank date from above, is there a method to retain the date from below? Thank you

[Reeza]

Could you just resort your data so that the blanks are at the end? I'm assuming you have other variables in this data set though, could they help with the sort?

If you have SAS ETS/PROC TIMESERIES could possibly fill it in as well.

[GS2]

I tried that and get an error that my by variable is not properly sorted

[novinosrin]

data have;
input ID    Date :mmddyy10.;
format date mmddyy10.;
cards;
1    .
1    .
1   .
1   01/17/2020
1    .
1    .
1   .
1   01/21/2020
2   01/18/2019
;
data temp;
 set have curobs=k;
 by id;
 if first.id then n=0;
 rn=k;
 if lag(date)>. and missing(date) and not first.id then n+1;
run;

proc sql;
create table want(drop=n) as
select id,n,max(date) as date format=mmddyy10.
from temp
group by id,n
order by rn;
quit;

[novinosrin]

Hi @GS2  Some Hash fun

data have;
input ID    Date :mmddyy10.;
format date mmddyy10.;
cards;
1    .
1    .
1   .
1   01/17/2020
1    .
1    .
1   .
1   01/21/2020
2   01/18/2019
2   .
2   01/22/2019
3   01/19/2019
3   .
3   01/23/2019
;

data want;
 if _n_=1 then do;
  dcl hash H (multidata:'y') ;
  h.definekey  ("id") ;
  h.definedata ("_iorc_") ;
  h.definedone () ;
 end;
 do _n_=1 by 1 until(last.id);
  set have end=z;
  by id;
  _iorc_=date;
  if _iorc_ then h.add();
 end;
 do _n_=1 to _n_;
  set have;
  if h.find()=0 and date=_iorc_ then h.removedup();
  date=_iorc_;
  output;
 end;
 h.clear();
run;

[GS2]

I am going to be honest that coding may be above my skill level. 

[novinosrin]

Hello @GS2  Prolly for the time being it is, but a fork lift driver with no formal comp/math/stat education before SAS can do it, you certainly can. Have that confidence. All the best!

[r_behata]

data have;
input ID    Date :mmddyy10.;
format date mmddyy10.;
cards;
1    .
1    .
1   .
1   01/21/2020
1    .
1    .
1   .
2   01/18/2019
;
run;

proc sort data=have;
 by id descending Date ;
run;

data want;
update have(obs=0) have;
by id;
output;
run;

[GS2]

This did not produce the results I wanted. It retained a date from the row above, which is from a different correct and therefore an incorrect date. 

[novinosrin · Accepted solution]

Hi @GS2  Please see if the simple approach below works for you. I think this is easy to follow and ,modify/edit to your needs. Please let us know if we can be of more help

data have;
input ID    Date :mmddyy10.;
format date mmddyy10.;
cards;
1    .
1    .
1   .
1   01/17/2020
1    .
1    .
1   .
1   01/21/2020
2   01/18/2019
2   .
2   01/22/2019
3   01/19/2019
3   .
3   01/23/2019
;
/*Get the rownumber*/
data temp;
set have curobs=k;
rownum=k;
run;

proc sort data=temp out=temp2;
by  id descending rownum;
run;

data temp3;
 update temp2(obs=0) temp2;
 by id ;
 output;
run;

proc sort data=temp3 out=want;
by rownum;
run;

[r_behata]

data have;
input ID    Date :mmddyy10.;
format date mmddyy10.;
cards;
1    .
1    .
1   .
1   01/17/2020
1    .
1    .
1   .
1   01/21/2020
2   01/18/2019
2   .
2   01/22/2019
3   01/19/2019
3   .
3   01/23/2019
;
run;

data want(rename=(date=date_old date2=date ));
array id_{1:10} _temporary_;

format date2 date9.;
do _n_=1 by 1 until(last.id | ^ missing(date))  ;
	set have;
	by id;

	id_[id]=date; 
end;

do until(last.id | ^missing(date));
	set have;
	by id;
	date2=id_[id]; output;
end;
run;