topic Re: Filling the missing dates in column in SAS Programming

Filling the missing dates in column

Di_Pat — Mon, 03 Oct 2016 18:50:18 GMT

Hello,

I have a dataset and need to calculate the applications through out the month they where touched and not touched and thus need to add dates on missing days. My dataset looks like below.

Actual dataset.

App# Modified Time

001 09/07/16 11:03:08

001 09/08/16 12:26:15

001 09/08/16 11:04:52

001 09/12/16 09:05:10

002 09/01/16 11:03:08

002 09/02/16 12:26:15

002 09/04/16 11:04:52

002 09/09/16 09:05:10

Correct output should look like below

App# Modified Time

001 09/07/16 11:03:08

001 09/08/16 12:26:15

001 09/08/16 11:04:52

001 09/09/16 01:01:01

001 09/10/16 01:01:01

001 09/11/16 01:01:01

001 09/12/16 09:05:10

002 09/01/16 11:03:08

002 09/02/16 12:26:15

002 09/03/16 01:01:01

002 09/04/16 11:04:52

002 09/05/16 01:01:01

002 09/06/16 01:01:01

002 09/07/16 01:01:01

002 09/08/16 01:01:01

002 09/09/16 11:04:52

Thank you.

Re: Filling the missing dates in column

set_all__ — Mon, 03 Oct 2016 19:10:35 GMT

Are date and time two different variables or one single variable?

Re: Filling the missing dates in column

Di_Pat — Mon, 03 Oct 2016 19:14:32 GMT

They are two different variables. For missing dates, time can be anything.

Re: Filling the missing dates in column

Shmuel — Mon, 03 Oct 2016 20:22:21 GMT

Assuming first date and last date per application exists, and that date is a SAS date,

then you can do:

data want;

set have;

by app;

if not first.app and not last.app then do;

save_date = date; drop save_date;

if save_date < lag(date) +1 then do;

do date = lag(date)+1 to save_date - 1;

/* time = ...whatever you prefer ... */

output;

end;

run;

Re: Filling the missing dates in column

Di_Pat — Mon, 03 Oct 2016 22:01:28 GMT

it does not work for me. It generates error for do loop. Thank you

Re: Filling the missing dates in column

Shmuel — Tue, 04 Oct 2016 03:11:18 GMT

try code:

set have;

by app;

save_date = date; prev_date=lag(date); diff = save_date - prev_date;

DROP save_date prev_date diff;

if first.app then output;

else do;

if diff ge 2 then do;

do date = prev_date +1 to save_date by 1;

/* time = ...whatever you preffer ... */

output;

end;

end; else output;

end;

run;

Re: Filling the missing dates in column

Ksharp — Tue, 04 Oct 2016 04:31:30 GMT

data have;
input App ModifiedTime anydtdtm32.;
date=datepart(modifiedtime);
format modifiedtime datetime. date date9.;
cards;
001 09/07/16 11:03:08
001 09/08/16 12:26:15
001 09/08/16 11:04:52
001 09/12/16 09:05:10
002 09/01/16 11:03:08
002 09/02/16 12:26:15
002 09/04/16 11:04:52
002 09/09/16 09:05:10
;
run;
proc sql;
create table temp as 
 select app,datepart(min(modifiedtime)) as min ,
            datepart(max(modifiedtime)) as max
  from have
   group by app;
quit;
data x;
 set temp;
 do date=min to max;
  output;
 end;
 format date date9.;
 keep app date;
run;
data want;
 merge have x;
 by app date;
 new=coalesce(modifiedtime,dhms(date,1,1,1));
 format new datetime.;
 drop date modifiedtime;
run;

Re: Filling the missing dates in column

Di_Pat — Tue, 04 Oct 2016 16:45:04 GMT

Yesterday I used your other code and solved it but it was way longer than what you have provided here.

Thank you. It works for me.

Re: Filling the missing dates in column

Di_Pat — Tue, 04 Oct 2016 16:46:05 GMT

Thank you. This works.

Re: Filling the missing dates in column

Ksharp — Wed, 05 Oct 2016 03:15:14 GMT

Yes. There are many ways in SAS can solve the same question.


data have;
input App ModifiedTime anydtdtm32.;
date=datepart(modifiedtime);
format modifiedtime datetime. date date9.;
cards;
001 09/07/16 11:03:08
001 09/08/16 12:26:15
001 09/08/16 11:04:52
001 09/12/16 09:05:10
002 09/01/16 11:03:08
002 09/02/16 12:26:15
002 09/04/16 11:04:52
002 09/09/16 09:05:10
;
run;
data want;
 merge have have(keep=app date rename=(app=_app date=_date) firstobs=2);
 output;
 if app=_app then do;
  do i=date+1 to _date-1;
   modifiedtime=dhms(i,1,1,1);output;
  end;
 end;
drop _: i date;
run;