Help aggregating a column in a data set

[BrinaLi]

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!

[Patrick · Accepted solution]

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]

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!