topic Re: Fill row with last non blank entry by group in New SAS User

Fill row with last non blank entry by group

GS2 — Tue, 21 Jan 2020 19:07:15 GMT

Using SAS 9.4

I have data that looks like this

ID Date

1 .

1 01/21/2020

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

ID Date

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

Re: Fill row with last non blank entry by group

Reeza — Tue, 21 Jan 2020 19:13:27 GMT

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.

Re: Fill row with last non blank entry by group

GS2 — Tue, 21 Jan 2020 19:18:45 GMT

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

Re: Fill row with last non blank entry by group

novinosrin — Tue, 21 Jan 2020 19:42:50 GMT


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;

Re: Fill row with last non blank entry by group

novinosrin — Tue, 21 Jan 2020 20:16:20 GMT

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;

Re: Fill row with last non blank entry by group

r_behata — Tue, 21 Jan 2020 20:15:23 GMT

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;

Re: Fill row with last non blank entry by group

GS2 — Tue, 21 Jan 2020 20:33:08 GMT

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

Re: Fill row with last non blank entry by group

GS2 — Tue, 21 Jan 2020 20:34:02 GMT

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.

Re: Fill row with last non blank entry by group

novinosrin — Tue, 21 Jan 2020 20:35:34 GMT

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!

Re: Fill row with last non blank entry by group

novinosrin — Tue, 21 Jan 2020 20:48:25 GMT

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;

Re: Fill row with last non blank entry by group

r_behata — Tue, 21 Jan 2020 21:21:10 GMT


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;