Hello,
I am looking for some help with the following. I am trying to create an indicator to show when the rep has taken a combined break over 30 minutes (or in this case over 1800 seconds). The reps have more than one "break" to select from. My table looks like this:
Date | employeeID | EmployeeID+Date | AuxCode Name | AuxStatus | Timeis |
17-Feb-24 | 1 | 1-45339 | Break | Busy | 900 |
17-Feb-24 | 1 | 1-45339 | Available | Available | 36878 |
17-Feb-24 | 1 | 1-45339 | Break Personal | Busy | 1000 |
17-Feb-24 | 2 | 2-45339 | Offline | Offline | 22830 |
17-Feb-24 | 2 | 2-45339 | Break | Busy | 86400 |
17-Feb-24 | 2 | 2-45339 | Available | Available | 15311 |
17-Feb-24 | 2 | 2-45339 | Logout-End Shift | Offline | 71088 |
17-Feb-24 | 3 | 3-45339 | Available | Available | 86400 |
17-Feb-24 | 3 | 3-45339 | Offline Work-8 | Busy | 86400 |
17-Feb-24 | 4 | 4-45339 | Available | Available | 173 |
17-Feb-24 | 4 | 4-45339 | Logout-End Shift | Offline | 86226 |
17-Feb-24 | 4 | 4-45339 | Break-2 | Busy | 86400 |
17-Feb-24 | 4 | 4-45339 | Break Personal | Busy | 86400 |
17-Feb-24 | 4 | 4-45339 | Available | Available | 39694 |
17-Feb-24 | 5 | 5-45339 | Offline | Offline | 46705 |
17-Feb-24 | 5 | 5-45339 | Available | Available | 86400 |
17-Feb-24 | 5 | 5-45339 | Break-2 | Busy | 86400 |
17-Feb-24 | 5 | 5-45339 | Available | Available | 3186 |
17-Feb-24 | 5 | 5-45339 | Offline | Offline | 54776 |
17-Feb-24 | 5 | 5-45339 | Stretch Break | Busy | 28437 |
for example, the indicator would flag that employee 1 took a combined break on 2/17/24 that was over 30 mins.
for something like this, is it best to create a separate table from this table? or is it possible to include it in this table as well?
I appreciate your help in advance! thanks!
If it's better to create a separate table or combine the data into a single table depends on what you're planning to do with the result. Below sample code for both options.
data work.have;
infile datalines dsd truncover dlm=',';
input Date:date11. employeeID EmployeeID_Date:$7. AuxCode_Name:$20. AuxStatus:$20. Timeis;
format date date9.;
datalines;
17-Feb-24,1,1-45339,Break,Busy,900
17-Feb-24,1,1-45339,Available,Available,36878
17-Feb-24,1,1-45339,Break Personal,Busy,700
17-Feb-24,2,2-45339,Offline,Offline,22830
17-Feb-24,2,2-45339,Break,Busy,86400
17-Feb-24,2,2-45339,Available,Available,15311
17-Feb-24,2,2-45339,Logout-End Shift,Offline,71088
17-Feb-24,3,3-45339,Available,Available,86400
17-Feb-24,3,3-45339,Offline Work-8,Busy,86400
17-Feb-24,4,4-45339,Available,Available,173
17-Feb-24,4,4-45339,Logout-End Shift,Offline,86226
17-Feb-24,4,4-45339,Break-2,Busy,86400
17-Feb-24,4,4-45339,Break Personal,Busy,86400
17-Feb-24,4,4-45339,Available,Available,39694
17-Feb-24,5,5-45339,Offline,Offline,46705
17-Feb-24,5,5-45339,Available,Available,86400
17-Feb-24,5,5-45339,Break-2,Busy,86400
17-Feb-24,5,5-45339,Available,Available,3186
17-Feb-24,5,5-45339,Offline,Offline,54776
17-Feb-24,5,5-45339,Stretch Break,Busy,28437
;
/* separate table */
proc sql;
/* create table work.separate_table as */
select date, employeeID, sum(timeis) as sum_break_time, sum(timeis)>1800 as over_30_ind
from work.have
where upcase(AuxCode_Name) like '%BREAK%'
group by date, employeeID
;
quit;
/* denormalised flat table */;
proc sql;
/* create table work.single_table as */
select l.*, coalesce(r.sum_break_time,0) as sum_break_time, coalesce(r.over_30_ind,0) as over_30_ind
from work.have l
left join
(
select date, employeeID, sum(timeis) as sum_break_time, sum(timeis)>1800 as over_30_ind
from work.have
where upcase(AuxCode_Name) like '%BREAK%'
group by date, employeeID
) r
on l.date=r.date and l.employeeID=r.employeeID
;
quit;
What many people appreciate is if the one asking the questions provides sample data already in a directly usable form (SAS data step code as done above) so we can spend our time answering your questions instead of preparing the sample data.
If it's better to create a separate table or combine the data into a single table depends on what you're planning to do with the result. Below sample code for both options.
data work.have;
infile datalines dsd truncover dlm=',';
input Date:date11. employeeID EmployeeID_Date:$7. AuxCode_Name:$20. AuxStatus:$20. Timeis;
format date date9.;
datalines;
17-Feb-24,1,1-45339,Break,Busy,900
17-Feb-24,1,1-45339,Available,Available,36878
17-Feb-24,1,1-45339,Break Personal,Busy,700
17-Feb-24,2,2-45339,Offline,Offline,22830
17-Feb-24,2,2-45339,Break,Busy,86400
17-Feb-24,2,2-45339,Available,Available,15311
17-Feb-24,2,2-45339,Logout-End Shift,Offline,71088
17-Feb-24,3,3-45339,Available,Available,86400
17-Feb-24,3,3-45339,Offline Work-8,Busy,86400
17-Feb-24,4,4-45339,Available,Available,173
17-Feb-24,4,4-45339,Logout-End Shift,Offline,86226
17-Feb-24,4,4-45339,Break-2,Busy,86400
17-Feb-24,4,4-45339,Break Personal,Busy,86400
17-Feb-24,4,4-45339,Available,Available,39694
17-Feb-24,5,5-45339,Offline,Offline,46705
17-Feb-24,5,5-45339,Available,Available,86400
17-Feb-24,5,5-45339,Break-2,Busy,86400
17-Feb-24,5,5-45339,Available,Available,3186
17-Feb-24,5,5-45339,Offline,Offline,54776
17-Feb-24,5,5-45339,Stretch Break,Busy,28437
;
/* separate table */
proc sql;
/* create table work.separate_table as */
select date, employeeID, sum(timeis) as sum_break_time, sum(timeis)>1800 as over_30_ind
from work.have
where upcase(AuxCode_Name) like '%BREAK%'
group by date, employeeID
;
quit;
/* denormalised flat table */;
proc sql;
/* create table work.single_table as */
select l.*, coalesce(r.sum_break_time,0) as sum_break_time, coalesce(r.over_30_ind,0) as over_30_ind
from work.have l
left join
(
select date, employeeID, sum(timeis) as sum_break_time, sum(timeis)>1800 as over_30_ind
from work.have
where upcase(AuxCode_Name) like '%BREAK%'
group by date, employeeID
) r
on l.date=r.date and l.employeeID=r.employeeID
;
quit;
What many people appreciate is if the one asking the questions provides sample data already in a directly usable form (SAS data step code as done above) so we can spend our time answering your questions instead of preparing the sample data.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.