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

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:

 

 

DateemployeeIDEmployeeID+DateAuxCode NameAuxStatusTimeis
17-Feb-2411-45339BreakBusy900
17-Feb-2411-45339AvailableAvailable36878
17-Feb-2411-45339Break PersonalBusy1000
17-Feb-2422-45339OfflineOffline22830
17-Feb-2422-45339BreakBusy86400
17-Feb-2422-45339AvailableAvailable15311
17-Feb-2422-45339Logout-End ShiftOffline71088
17-Feb-2433-45339AvailableAvailable86400
17-Feb-2433-45339Offline Work-8Busy86400
17-Feb-2444-45339AvailableAvailable173
17-Feb-2444-45339Logout-End ShiftOffline86226
17-Feb-2444-45339Break-2Busy86400
17-Feb-2444-45339Break PersonalBusy86400
17-Feb-2444-45339AvailableAvailable39694
17-Feb-2455-45339OfflineOffline46705
17-Feb-2455-45339AvailableAvailable86400
17-Feb-2455-45339Break-2Busy86400
17-Feb-2455-45339AvailableAvailable3186
17-Feb-2455-45339OfflineOffline54776
17-Feb-2455-45339Stretch BreakBusy28437

 

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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.

View solution in original post

2 REPLIES 2
Patrick
Opal | Level 21

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.

BrinaLi
Fluorite | Level 6
Thank you!!! this is exactly what I was looking for. My apologies about the sample data! I will be sure to provide it in the correct format in the future.

Thank you again!

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 529 views
  • 1 like
  • 2 in conversation