I am attaching the problem query below & some of the log output. I've changed some of the variable and table names. This is the worst runtime I've ever seen for it, but then the next day it ran in ~7 minutes.
A unique identifier for call_data & problem_table would be employee_date_key + call_skill, but the employee data won't have call_skill, it only has employee_date_key (this is unique for that table, it will only have 1 row per employee_date_key).
PROC SQL;
CREATE TABLE gridwork.problem_table as
SELECT DISTINCT
t1.employee_date_key
, t1.EMP_NNUMBER
, t1.department
, t1.job_name
, t1.targets_key
, t1.employee_tenure
, t2.call_date
, intnx('month',t2.call_date,0,"BEGINNING") as begofmonth
, t2.call_skill
, t2.Skill_JoinFmtCSO
, t2.TRANSFER_LOCATION
, . as RESOLVED_NUM
, sum(t2.ACD_CALL_COUNT)
, sum(case when exc.exclude_AHT_YN = 'Y' then . else t2.REP_TALK_SEC end) as TALK_TIME_SEC
, sum(case when exc.exclude_AHT_YN = 'Y' then .
when t2.ACW_SEC > 3599 then 3600 + t2.REP_TALK_SEC
else t2.REP_TALK_SEC + t2.ACW_SEC end) as KPI_NUM
, sum(case when exc.exclude_aht_yn = 'Y'
or t2.ACD_CALL_COUNT = 0 then .
else t2.ACD_CALL_COUNT end) as KPI_DENOM
, sum(case when exc.exclude_acw_yn = 'Y' then .
when t2.ACW_SEC > 3599 then 3600
else t2.ACW_SEC end) as KPI2_NUM
, sum(case when t2.ACD_CALL_COUNT = 0
or exc.exclude_acw_yn = 'Y' then .
else t2.ACD_CALL_COUNT end) as KPI2_DENOM
, sum(t2.TRANSFER_CALL_COUNT1) as TRANSFER_NUM
, sum(t2.ACD_CALL_COUNT) as TRANSFER_DENOM
, sum(t2.LEAD_COUNT)
, sum(t2.ACCEPTABLE_CALL_COUNT)
, count(t2.ACCEPTABLE_CALL_COUNT)
, sum(t2.ANSWER_TIME_SEC) as KPI3_NUM
, sum(t2.LEADS_ELIG_COUNT)
, t1.Blended_tenure_bucket
, t1.Blended_StartDate
from CUBE.employee_data t1
join GRIDWORK.call_data t2
on (t1.EMP_NNUMBER = t2.EMP_PIN
and t1.CLNDR_DT = t2.call_date
and t1.l1_status = 'Active')
left join dat.bl_exceptions exc
on (t1.CLNDR_DT between exc.FROM_DATE and exc.TO_DATE
and t1.department = exc.TEAM
and (exc.exclude_aht_yn = 'Y' or exc.exclude_acw_yn = 'Y'))
where t1.CLNDR_DT >= /*&begofCurrentYear.*/ mdy(1,1,&CurrentYear)
2023-06-29T08:06:07,943 - SYMBOLGEN: Macro variable CURRENTYEAR resolves to 2023
and t1.CHANNEL = 'Business Lines'
group by 1,2,3,4,5,6,7,8,9,10,11
;
2023-06-29T08:06:08,364 - Libref=CUBE Engine=V9 Member=employee_data MemberType=DATA Openmode=INPUT Path={file path}
2023-06-29T08:06:08,372 - Libref=GRIDWORK Engine=V9 Member=call_data MemberType=DATA Openmode=INPUT Path={file path}
2023-06-29T08:06:08,676 - Libref=DAT Engine=V9 Member=BL_EXCEPTIONS MemberType=DATA Openmode=INPUT Path={path}
2023-06-29T08:06:08,678 - NOTE: The query requires remerging summary statistics back with the original data.
2023-06-29T08:06:08,680 - Libref=GRIDWORK Engine=V9 Member=problem_table MemberType=DATA Openmode=OUTPUT Path={path}
2023-06-29T08:06:08,688 - NOTE: SAS threaded sort was used.
2023-06-29T08:48:32,032 - NOTE: Compressing data set GRIDWORK.problem_table decreased size by 96.89 percent.
2023-06-29T08:48:32,090 - Compressed is 134 pages; un-compressed would require 4305 pages.
2023-06-29T08:48:32,122 - NOTE: Table GRIDWORK.problem_table created, with 43042 rows and 27 columns.
2023-06-29T08:48:32,122 -
2023-06-29T08:48:35,166 - 1593 +QUIT;
2023-06-29T08:48:35,196 - NOTE: PROCEDURE SQL used (Total process time):
2023-06-29T08:48:35,196 - real time 42:27.25
2023-06-29T08:48:35,196 - user cpu time 56.07 seconds
2023-06-29T08:48:35,196 - system cpu time 2:33.86
2023-06-29T08:48:35,196 - memory 6533820.03k
2023-06-29T08:48:35,196 - OS Memory 6555652.00k
2023-06-29T08:48:35,196 - Timestamp 06/29/2023 08:48:35 AM
2023-06-29T08:48:35,196 - Step Count 20 Switch Count 0
2023-06-29T08:48:35,196 - Page Faults 0
2023-06-29T08:48:35,196 - Page Reclaims 3245409
2023-06-29T08:48:35,196 - Page Swaps 0
2023-06-29T08:48:35,196 - Voluntary Context Switches 107048
2023-06-29T08:48:35,196 - Involuntary Context Switches 37457
2023-06-29T08:48:35,196 - Block Input Operations 0
2023-06-29T08:48:35,196 - Block Output Operations 0
