Breakdown DateTime into hour segment in EG

[derickloo]

I has a table in EG which I which to breakdown the date time into hour segment.  The data as below

ID        Start Date                                  End Date                         Task                      Resource

537    2014-06-27 20:30:00.000       2014-06-28 08:30:00.000 Washing Machine   187 

I wish to breakdown the data as below in EG

ID      Date               Start Time       End Time   Total Time (Minutes)          Task                      Resource

537   2014-06-27    20:30              21:00          30                                   Washing Machine   187 

537   2014-06-27    21:00              22:00          60                                   Washing Machine   187 

537   2014-06-27    22:30              23:00          60                                   Washing Machine   187 

537   2014-06-28    00:00              01:00          60                                   Washing Machine   187 

537   2014-06-27    02:00              03:00          60                                   Washing Machine   187 

Is it possible I breakdown my data become like that?  How can I do this in EG?  What function should I use?

[Kurt_Bremser · Accepted solution]

Don't know if I read your intentions right, as your suggested result does not consistently follow a rule.

This code:

data have;
infile cards dlm=',';
input id start_date : e8601dt23.3 end_date: e8601dt23.3 Task :$20. resource;
format start_date end_date datetime23.3;
cards;
537,2014-06-27 20:30:00.000,2014-06-28 08:30:00.000,Washing Machine,187
;
run;

data want (keep=id date start_time end_time total_time task resource);
* this just for column order: ;
format id date start_time end_time total_time task resource;
set have;
format
  date yymmddd10.
  start_time end_time hhmm5.
;
start_time = hour(timepart(start_date));
do until (start_date > end_date);
  date = datepart(start_date);
  start_time = timepart(start_date);
  end_time = intnx('hour',start_time,0,'end');
  if date * 86400 + end_time > end_date then end_time = timepart(end_date);
  total_time = round((end_time - start_time) / 60,1);
  output;
  start_date = intnx('hour',start_date,1,'begin');
end;
run;

proc print;
run;

creates this result:

                            start_                total_
Obs     id          date     time     end_time     time          task          resour

  1    537    2014-06-27    20:30      21:00        30      Washing Machine       187
  2    537    2014-06-27    21:00      22:00        60      Washing Machine       187
  3    537    2014-06-27    22:00      23:00        60      Washing Machine       187
  4    537    2014-06-27    23:00      24:00        60      Washing Machine       187
  5    537    2014-06-28     0:00       1:00        60      Washing Machine       187
  6    537    2014-06-28     1:00       2:00        60      Washing Machine       187
  7    537    2014-06-28     2:00       3:00        60      Washing Machine       187
  8    537    2014-06-28     3:00       4:00        60      Washing Machine       187
  9    537    2014-06-28     4:00       5:00        60      Washing Machine       187
 10    537    2014-06-28     5:00       6:00        60      Washing Machine       187
 11    537    2014-06-28     6:00       7:00        60      Washing Machine       187
 12    537    2014-06-28     7:00       8:00        60      Washing Machine       187
 13    537    2014-06-28     8:00       8:30        30      Washing Machine       187

[RW9]

How do you know to split it into 30, 60, 60... sections, is that always fixed?  If so:

data have;
  id=537; start_date="2014-06-27T20:30:00.000"dt; end_date="2014-06-28T08:30:00.000"dt; task="Washing Machine"; resource=187;
  format start_date end_date datetime.; 
run;
data want; 
  set have;
  date=datepart(start_date);
  start_time=timepart(start_date);
  end_time=start_time+"00:30"t;
  total_time=30;
  output;
  start_date=start_date+"00:30"t;
  do while (start_date <= end_date);
    date=datepart(start_date);
    start_time=timepart(start_date);
    end_time=start_time+"01:00"t;
    total_time=60;
    output;
    start_date=start_date+"01:00"t;
  end;
  format date date9. start_time end_time time5.;
run;

[RahulG]

format Date yymmdd10.  Start_time End_time time5.

Date=datetime();
Date=datepart(Start Date);
Start_time=hms(hour(Start Date), minute(Start Date),second(Start Date));

End_time=hms(hour(End Date), minute(End Date),second(End Date));
Total_minutes=(End_time-Start_time)/60 ;

[Ksharp]

data have;
infile cards dlm=',';
input id start_date : e8601dt23.3 end_date: e8601dt23.3 Task :$20. resource;
format start_date end_date datetime23.3;
cards;
537,2014-06-27 20:30:00.000,2014-06-28 08:30:00.000,Washing Machine,187
;
run;
data want;
 set have;
 do i=start_date to end_date ;
  hour=hour(timepart(i));
  lag_hour=lag(hour);
  if hour ne lag_hour then do;
   end_time=timepart(i);
   total_time=(i-lag_i)/60;
   if not missing(lag_hour) then output;
   start_time=end_time;
   lag_i=i;
   date=datepart(i);
  end;
 end;
format start_time end_time time8. date yymmdd10. ;
drop i start_date end_date hour lag_hour lag_i ;
run;