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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.