BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Di_Pat
Fluorite | Level 6

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

 

 

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

9 REPLIES 9
set_all__
Fluorite | Level 6
Are date and time two different variables or one single variable?
Di_Pat
Fluorite | Level 6

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

Shmuel
Garnet | Level 18

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;

Di_Pat
Fluorite | Level 6

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

Shmuel
Garnet | Level 18

 

 

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;

 

 

Di_Pat
Fluorite | Level 6

Thank you. This works. 

Ksharp
Super User
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;
 

Di_Pat
Fluorite | Level 6

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.

Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3426 views
  • 3 likes
  • 4 in conversation