DATA Step, Macro, Functions and more

Filling the missing dates in column

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Filling the missing dates in column

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.

 


Accepted Solutions
Solution
‎10-04-2016 12:43 PM
Trusted Advisor
Posts: 1,392

Re: Filling the missing dates in column

 

 

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;

 

 

View solution in original post


All Replies
Occasional Contributor
Posts: 8

Re: Filling the missing dates in column

Are date and time two different variables or one single variable?
Occasional Contributor
Posts: 13

Re: Filling the missing dates in column

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

Trusted Advisor
Posts: 1,392

Re: Filling the missing dates in column

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;

        end;

run;

Occasional Contributor
Posts: 13

Re: Filling the missing dates in column

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

Solution
‎10-04-2016 12:43 PM
Trusted Advisor
Posts: 1,392

Re: Filling the missing dates in column

 

 

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;

 

 

Occasional Contributor
Posts: 13

Re: Filling the missing dates in column

Thank you. This works. 

Super User
Posts: 9,682

Re: Filling the missing dates in column

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;
 

Occasional Contributor
Posts: 13

Re: Filling the missing dates in column

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.

Super User
Posts: 9,682

Re: Filling the missing dates in column

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 493 views
  • 2 likes
  • 4 in conversation