Thanks! That's helpful. to answer your question about the indemnified variable, I believe the variable is the "status". The status will show expired based on the due date. For example, a course is due every 12 months and it's due the last day of that 12th month. However, the status is showing expired on that last day when it shouldn't actually exire until the NEXT day: proc sql; create table _course_summary as select year(file_dt) as yr ,month(file_dt) as mo ,o.delta_department ,o.contractor_ind ,o.hr_stn_cd ,sum(case when o.status = 'EXPIRED' then 1 else 0 end) as c_expired ,count(distinct case when o.status = 'EXPIRED' then o.empl_nbr else '' end) as e_expired from _lms_raw o where o.status = 'EXPIRED' group by 1, 2, 3, 4, 5 order by 1, 2, 3, 4, 5; create table _dep_summary as select p.delta_department ,p.contractor_ind ,p.hr_stn_cd ,count(distinct p.p_unique_key) as emp_cnt from trn._person p where p.delta_department not in ('','XXX') group by 1, 2, 3 order by 1, 2, 3; run;
... View more