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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11

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

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 4 replies
  • 2471 views
  • 3 likes
  • 5 in conversation