Hello Experts, I am new to SAS programming and I am supporting a PROD ETL process written in SAS. It connects to Oracle DB and the processing is between Oracle and SAS. These processes run every day. These ETL processes run for more than 17hrs. Looking to tune these processes, we noticed that at one particular point the script seems to hang for more than 8 hrs. On a good day this step completes in couple of mins. The ETL processes are inter-related. Each step depends on its previous step. Attached is a pic of the WORK Library where I see the server is chugging to create big TMP utility files. Please help me understand the bottleneck here. Here is a snippet that just hangs: NDS_RDB is our oracle database. RDB is the SAS library. LDF_DIMENSIONAL_DATA is an object created in the previous step. It creates BASE_FOODBORNE, LINKED_FOODBORNE and ALL_FOODBORNE objects in the WORK library. In the WORK library I see BASE_FOODBORNE and LINKED_FOODBORNE created. ALL_FOODBORNE is not seen and I see temp utility files growing big. PROC SQL; DROP TABLE NBS_RDB.LDF_FOODBORNE; CREATE TABLE BASE_FOODBORNE AS SELECT * FROM LDF_DIMENSIONAL_DATA WHERE PHC_CD IN (SELECT CONDITION_CD FROM NBS_RDB.LDF_DATAMART_TABLE_REF WHERE DATAMART_NAME = 'LDF_FOODBORNE'); CREATE TABLE LINKED_FOODBORNE AS SELECT GEN_LDF.*, INV.INVESTIGATION_KEY, INV.INV_LOCAL_ID AS INVESTIGATION_LOCAL_ID 'INVESTIGATION_LOCAL_ID', INV.CASE_OID AS PROGRAM_JURISDICTION_OID 'PROGRAM_JURISDICTION_OID', GEN.PATIENT_KEY, PATIENT.PATIENT_LOCAL_ID AS PATIENT_LOCAL_ID 'PATIENT_LOCAL_ID', CONDITION.CONDITION_SHORT_NM AS DISEASE_NAME 'DISEASE_NAME', CONDITION.CONDITION_CD, GEN_LDF.PHC_CD FROM BASE_FOODBORNE GEN_LDF INNER JOIN NBS_RDB.INVESTIGATION INV ON GEN_LDF.INVESTIGATION_UID=INV.CASE_UID INNER JOIN NBS_RDB.GENERIC_CASE GEN ON GEN.INVESTIGATION_KEY=INV.INVESTIGATION_KEY INNER JOIN NBS_RDB.CONDITION ON CONDITION.CONDITION_KEY= GEN.CONDITION_KEY INNER JOIN NBS_RDB.D_PATIENT PATIENT ON PATIENT.PATIENT_KEY=GEN.PATIENT_KEY ORDER BY INVESTIGATION_UID; CREATE TABLE ALL_FOODBORNE AS SELECT A.*, B.DATAMART_COLUMN_NM AS DM 'DM' FROM NBS_RDB.LDF_DATAMART_COLUMN_REF B FULL OUTER JOIN LINKED_FOODBORNE A ON A.LDF_UID= B.LDF_UID WHERE (B.LDF_PAGE_SET ='OTHER' OR B.CONDITION_CD IN (SELECT CONDITION_CD FROM NBS_RDB.LDF_DATAMART_TABLE_REF WHERE DATAMART_NAME = 'LDF_FOODBORNE') ) ORDER BY INVESTIGATION_UID; QUIT;
... View more