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?
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
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
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;
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 ;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.