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 more