Yes, I have added %put &snapshot_id.; in the precode of DIS-JOB-BGIS-108-001 but still not work. And later I added %global snapshot_id; in the precode of A_testing and still not working. I have checked that I haven't used any parameter for any transformation. Attached are the pictures of the precode of the 2 jobs after accepting advise from @Hai.kuo and you. Here are the codes for the jobs: 1) A_testing Code: /**************************************************************************** * Job: A_testing A5NBTOIY.BV0004VJ * * Description: * * * * Metadata Server: dcdissas53v.cdisdev.chp.hksarg * * Port: 8561 * * Location: /DIS/Jobs * * * * Server: SASApp A5NBTOIY.AS000003 * * * * Source Table: REPORT_TO_DI_MAPPING - A5NBTOIY.BD0000QH * * SOL.REPORT_TO_DI_MAPPING * * Target Table: RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL * * RPTIMAGE.RST_DAT_BGIS_108_001 * * * * Generated on: 2015年03月23日 星期一 下午04時42分57秒 CST * * Generated by: super2@CDISDEV * * Version: SAS Data Integration Studio 4.9 * ****************************************************************************/ /* Generate the process id for job */ %put Process ID: &SYSJOBID; /* General macro variables */ %let jobID = %quote(A5NBTOIY.BV0004VJ); %let etls_jobName = %nrquote(A_testing); %let etls_userID = %nrquote(super2@CDISDEV); %global applName; data _null_; applName="SAS Data Integration Studio"; call symput('applName',%nrstr(applName)); run; /* Performance Statistics require ARM_PROC sub-system */ %macro etls_startPerformanceStats; %log4sas(); %log4sas_logger(Perf.ARM, 'level=info'); options armagent=log4sas armsubsys=(ARM_PROC); %global _armexec; %let _armexec = 1; %perfinit(applname="&applName"); %global etls_recnt; %let etls_recnt=-1; %mend; %etls_startPerformanceStats; %macro etls_setArmagent; %let armagentLength = %length(%sysfunc(getoption(armagent))); %if (&armagentLength eq 0) %then %do; %log4sas(); %log4sas_logger(Perf.ARM, 'level=info'); options armagent=log4sas armsubsys=(ARM_PROC); %end; %mend etls_setArmagent; %macro etls_setPerfInit; %if "&_perfinit" eq "0" %then %do; %etls_setArmagent; %global _armexec; %let _armexec = 1; %perfinit(applname="&applName"); %end; %mend etls_setPerfInit; /* Setup to capture return codes */ %global job_rc trans_rc sqlrc syscc; %let sysrc = 0; %let job_rc = 0; %let trans_rc = 0; %let sqlrc = 0; %let syscc = 0; %global etls_stepStartTime; /* initialize syserr to 0 */ data _null_; run; %macro rcSet(error); %if (&error gt &trans_rc) %then %let trans_rc = &error; %if (&error gt &job_rc) %then %let job_rc = &error; %mend rcSet; %macro rcSetDS(error); if &error gt input(symget('trans_rc'),12.) then call symput('trans_rc',trim(left(put(&error,12.)))); if &error gt input(symget('job_rc'),12.) then call symput('job_rc',trim(left(put(&error,12.)))); %mend rcSetDS; /* Create metadata macro variables */ %let IOMServer = %nrquote(SASApp); %let metaPort = %nrquote(8561); %let metaServer = %nrquote(dcdissas53v.cdisdev.chp.hksarg); /* Set metadata options */ options metaport = &metaPort metaserver = "&metaServer"; /* Setup for capturing job status */ %let etls_startTime = %sysfunc(datetime(),datetime.); %let etls_recordsBefore = 0; %let etls_recordsAfter = 0; %let etls_lib = 0; %let etls_table = 0; %global etls_debug; %macro etls_setDebug; %if %str(&etls_debug) ne 0 %then OPTIONS MPRINT%str(;); %mend; %etls_setDebug; /*---- Start of Pre-Process Code ----*/ %global snapshot_id; %let snapshot_id = 20171231; /*---- End of Pre-Process Code ----*/ %rcSet(&syserr); %rcSet(&sqlrc); /*==========================================================================* * Step: Retrieve Related Rows A5NBTOIY.BY000BI9 * * Transform: Extract * * Description: * * * * Source Table: REPORT_TO_DI_MAPPING - A5NBTOIY.BD0000QH * * SOL.REPORT_TO_DI_MAPPING * * Target Table: Extract - work.WEGSOOZ A5NBTOIY.C600087E * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY000BI9); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); /* Access the data for SOLITARY */ LIBNAME SOL BASE "\\dcdissas54v\sas\CDIS\Solitary File"; %rcSet(&syslibrc); %let etls_recCheckExist = 0; %let etls_recnt = 0; %macro etls_recordCheck; %let etls_recCheckExist = %eval(%sysfunc(exist(SOL.REPORT_TO_DI_MAPPING, DATA)) or %sysfunc(exist(SOL.REPORT_TO_DI_MAPPING, VIEW))); %if (&etls_recCheckExist) %then %do; %local etls_syntaxcheck; %let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck)); /* Turn off syntaxcheck option to perform following steps */ options nosyntaxcheck; proc contents data = SOL.REPORT_TO_DI_MAPPING out = work.etls_contents(keep = nobs) noprint; run; data _null_; set work.etls_contents (obs = 1); call symput("etls_recnt", left(put(nobs,32.))); run; proc datasets lib = work nolist nowarn memtype = (data view); delete etls_contents; quit; /* Reset syntaxcheck option to previous setting */ options &etls_syntaxcheck; %end; %mend etls_recordCheck; %etls_recordCheck; %let SYSLAST = %nrquote(SOL.REPORT_TO_DI_MAPPING); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; /*---- Map the columns ----*/ proc datasets lib = work nolist nowarn memtype = (data view); delete WEGSOOZ; quit; %put %str(NOTE: Mapping columns ...); proc sql; create view work.WEGSOOZ as select FLAG, REPORT, JOB_SEQUENCE label = 'JOB_SEQUENCE', DI_JOB from &SYSLAST where REPORT='RPT-BGIS-022W' ; quit; %let SYSLAST = work.WEGSOOZ; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Retrieve Related Rows **/ /*==========================================================================* * Step: Create Flag Macro A5NBTOIY.BY000BJ5 * * Transform: User Written * * Description: * * * * Source Table: Extract - work.WEGSOOZ A5NBTOIY.C600087E * * Target Table: User Written - work.WI81FRR A5NBTOIY.C600087U * * * * User Written: SourceCode * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY000BJ5); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = -1; %let SYSLAST = %nrquote(work.WEGSOOZ); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|UserWritten), metrNam6=_DISROWCNT, metrDef6=Count32) ; %let _INPUT_count = 1; %let _INPUT = work.WEGSOOZ; %let _INPUT_connect = ; %let _INPUT_engine = ; %let _INPUT_memtype = VIEW; %let _INPUT_options = %nrquote(); %let _INPUT_alter = %nrquote(); %let _INPUT_path = %nrquote(/Extract_A5NBTOIY.C600087E%(WorkTable%)); %let _INPUT_type = 1; %let _INPUT_label = %nrquote(); %let _INPUT1 = work.WEGSOOZ; %let _INPUT1_connect = ; %let _INPUT1_engine = ; %let _INPUT1_memtype = VIEW; %let _INPUT1_options = %nrquote(); %let _INPUT1_alter = %nrquote(); %let _INPUT1_path = %nrquote(/Extract_A5NBTOIY.C600087E%(WorkTable%)); %let _INPUT1_type = 1; %let _INPUT1_label = %nrquote(); %let _OUTPUT_count = 1; %let _OUTPUT = work.WI81FRR; %let _OUTPUT_connect = ; %let _OUTPUT_engine = ; %let _OUTPUT_memtype = DATA; %let _OUTPUT_options = %nrquote(); %let _OUTPUT_alter = %nrquote(); %let _OUTPUT_path = %nrquote(/User Written_A5NBTOIY.C600087U%(WorkTable%)); %let _OUTPUT_type = 1; %let _OUTPUT_label = %nrquote(); %let _OUTPUT_col_count = 0; %let _OUTPUT1 = work.WI81FRR; %let _OUTPUT1_connect = ; %let _OUTPUT1_engine = ; %let _OUTPUT1_memtype = DATA; %let _OUTPUT1_options = %nrquote(); %let _OUTPUT1_alter = %nrquote(); %let _OUTPUT1_path = %nrquote(/User Written_A5NBTOIY.C600087U%(WorkTable%)); %let _OUTPUT1_type = 1; %let _OUTPUT1_label = %nrquote(); %let _OUTPUT1_col_count = 0; /*---- Start of User Written Code ----*/ data _null_; set work.WEGSOOZ ; call symput('flag',flag); run; /*---- End of User Written Code ----*/ %rcSet(&syserr); %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Create Flag Macro **/ /*==========================================================================* * Step: Conditional Start A5NBTOIY.BY000BIJ * * Transform: Conditional Start * * Description: * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY000BIJ); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let ETLS_SYSLAST = &SYSLAST; /*---- Start of Pre-Process Code ----*/ %let snapshot_id = 20161231; /*---- End of Pre-Process Code ----*/ %rcSet(&syserr); %rcSet(&sqlrc); %let SYSLAST = &ETLS_SYSLAST; %macro etls_conditionWH2ICMT; %local etls_conditionTrue; %let etls_conditionTrue = %eval(&flag=N); %if (&etls_conditionTrue=0) %then %do; %put ETLS_DIAG: Condition flow did NOT execute, condition was &flag=N; %goto exitetls_conditionWH2ICMT; %end; %else %do; %put ETLS_DIAG: Condition flow did execute, condition was &flag=N; %end; /*==========================================================================* * Step: Set Flag to Y A5NBTOIY.BY000BJ6 * * Transform: User Written * * Description: * * * * Target Table: User Written - work.WI844FW A5NBTOIY.C600087V * * * * User Written: SourceCode * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY000BJ6); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|UserWritten), metrNam6=_DISROWCNT, metrDef6=Count32) ; %let _INPUT_count = 0; %let _OUTPUT_count = 1; %let _OUTPUT = work.WI844FW; %let _OUTPUT_connect = ; %let _OUTPUT_engine = ; %let _OUTPUT_memtype = DATA; %let _OUTPUT_options = %nrquote(); %let _OUTPUT_alter = %nrquote(); %let _OUTPUT_path = %nrquote(/User Written_A5NBTOIY.C600087V%(WorkTable%)); %let _OUTPUT_type = 1; %let _OUTPUT_label = %nrquote(); %let _OUTPUT_col_count = 0; %let _OUTPUT1 = work.WI844FW; %let _OUTPUT1_connect = ; %let _OUTPUT1_engine = ; %let _OUTPUT1_memtype = DATA; %let _OUTPUT1_options = %nrquote(); %let _OUTPUT1_alter = %nrquote(); %let _OUTPUT1_path = %nrquote(/User Written_A5NBTOIY.C600087V%(WorkTable%)); %let _OUTPUT1_type = 1; %let _OUTPUT1_label = %nrquote(); %let _OUTPUT1_col_count = 0; /*---- Start of User Written Code ----*/ data SOL.REPORT_TO_DI_MAPPING; set SOL.REPORT_TO_DI_MAPPING; if REPORT='RPT-BGIS-022W' then FLAG='Y'; run; /*---- End of User Written Code ----*/ %rcSet(&syserr); %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Set Flag to Y **/ /**************************************************************************** * Job: DIS-JOB-BGIS-108-001 A5NBTOIY.BV00004F * * Description: * * * * Metadata Server: dcdissas53v.cdisdev.chp.hksarg * * Port: 8561 * * Location: /DIS/Jobs * * * * Server: SASApp A5NBTOIY.AS000003 * * * * Source Tables: DIS_LOG - BGIS.DIS_LOG A5NBTOIY.BD00010K * * WEEK_NUMBER - SOL.WEEK_NUMBER A5NBTOIY.BD00000A * * CalculatedResult_AED - A5NBTOIY.BD0000BO * * Sherlock.CalculatedResult_AED * * SyndromeGroup_AED - A5NBTOIY.BD0000F0 * * Sherlock.SyndromeGroup_AED * * SnapshotSatScanResult_AED - A5NBTOIY.BD0000EJ * * Sherlock.SnapshotSatScanResult_AED * * SatScanStatus_AED - A5NBTOIY.BD000434 * * ShLog.SatScanStatus_AED * * Target Tables: RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL * * RPTIMAGE.RST_DAT_BGIS_108_001 * * RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL * * RPTIMAGE.RST_DAT_BGIS_108_001 * * * * Generated on: 2015年03月23日 星期一 下午04時42分57秒 CST * * Generated by: super2@CDISDEV * * Version: SAS Data Integration Studio 4.9 * ****************************************************************************/ /* Generate the process id for job */ %put Process ID: &SYSJOBID; /* General macro variables */ %let jobID = %quote(A5NBTOIY.BV00004F); %let etls_jobName = %nrquote(DIS-JOB-BGIS-108-001); %let etls_userID = %nrquote(super2@CDISDEV); /* Create metadata macro variables */ %let IOMServer = %nrquote(SASApp); %let metaPort = %nrquote(8561); %let metaServer = %nrquote(dcdissas53v.cdisdev.chp.hksarg); /* Set metadata options */ options metaport = &metaPort metaserver = "&metaServer"; /* Setup for capturing job status */ %let etls_startTime = %sysfunc(datetime(),datetime.); %let etls_recordsBefore = 0; %let etls_recordsAfter = 0; %let etls_lib = 0; %let etls_table = 0; %global etls_debug; %macro etls_setDebug; %if %str(&etls_debug) ne 0 %then OPTIONS MPRINT%str(;); %mend; %etls_setDebug; /*---- Start of Pre-Process Code ----*/ *%LET snapshot_id=20131231; %put &snapshot_id.; /*---- End of Pre-Process Code ----*/ %rcSet(&syserr); %rcSet(&sqlrc); /*==========================================================================* * Step: Extract A5NBTOIY.BY0002HO * * Transform: Extract * * Description: * * * * Source Table: DIS_LOG - BGIS.DIS_LOG A5NBTOIY.BD00010K * * Target Table: Extract - work.W1ALZ89 A5NBTOIY.C60001MW * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HO); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); /* Access the data for CDIS_BGIS */ LIBNAME BGIS ORACLE DBSERVER_MAX_BYTES=1 DBCLIENT_MAX_BYTES=1 PATH=ANASIT0 SCHEMA=CDIS_BGIS AUTHDOMAIN="bgisdbAuth" ; %rcSet(&syslibrc); %let etls_recnt = 0; %macro etls_recordCheck; %let etls_recCheckExist = %eval(%sysfunc(exist(BGIS.DIS_LOG, DATA)) or %sysfunc(exist(BGIS.DIS_LOG, VIEW))); %if (&etls_recCheckExist) %then %do; proc sql noprint; select count(*) into :etls_recnt from BGIS.DIS_LOG; quit; %end; %mend etls_recordCheck; %etls_recordCheck; %let SYSLAST = %nrquote(BGIS.DIS_LOG); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; /*---- Map the columns ----*/ proc datasets lib = work nolist nowarn memtype = (data view); delete W1ALZ89; quit; %put %str(NOTE: Mapping columns ...); proc sql; create view work.W1ALZ89 as select DIS_JOB_ID, LAST_SNAPSHOT_ID, RUN_DATE, REMARK from &SYSLAST ; quit; %let SYSLAST = work.W1ALZ89; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Extract **/ /*==========================================================================* * Step: Extract A5NBTOIY.BY0002HP * * Transform: Extract * * Description: * * * * Source Table: WEEK_NUMBER - SOL.WEEK_NUMBER A5NBTOIY.BD00000A * * Target Table: Extract - work.W1AQIJO A5NBTOIY.C60001MX * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HP); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recCheckExist = 0; %let etls_recnt = 0; %macro etls_recordCheck; %let etls_recCheckExist = %eval(%sysfunc(exist(SOL.WEEK_NUMBER, DATA)) or %sysfunc(exist(SOL.WEEK_NUMBER, VIEW))); %if (&etls_recCheckExist) %then %do; %local etls_syntaxcheck; %let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck)); /* Turn off syntaxcheck option to perform following steps */ options nosyntaxcheck; proc contents data = SOL.WEEK_NUMBER out = work.etls_contents(keep = nobs) noprint; run; data _null_; set work.etls_contents (obs = 1); call symput("etls_recnt", left(put(nobs,32.))); run; proc datasets lib = work nolist nowarn memtype = (data view); delete etls_contents; quit; /* Reset syntaxcheck option to previous setting */ options &etls_syntaxcheck; %end; %mend etls_recordCheck; %etls_recordCheck; %let SYSLAST = %nrquote(SOL.WEEK_NUMBER); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; /*---- Map the columns ----*/ proc datasets lib = work nolist nowarn memtype = (data view); delete W1AQIJO; quit; %put %str(NOTE: Mapping columns ...); proc sql; create view work.W1AQIJO as select odate from &SYSLAST where year(ODATE)>=input(substr("&SNAPSHOT_ID",1,4),BEST.)-2 and ODATE<=input("&SNAPSHOT_ID",yymmdd8.)-1 ; quit; %let SYSLAST = work.W1AQIJO; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Extract **/ /*==========================================================================* * Step: Join A5NBTOIY.BY0002HR * * Transform: Join * * Description: * * * * Source Table: SatScanStatus_AED - A5NBTOIY.BD000434 * * ShLog.SatScanStatus_AED * * Target Table: Join - work.W16944G A5NBTOIY.C60001MZ * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HR); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); /* Access the data for Sherlock_Log */ LIBNAME ShLog ODBC DATASRC=sherlock_log_chp SCHEMA=dbo AUTHDOMAIN="SherlLogCHPAuth" ; %rcSet(&syslibrc); %let etls_recnt = 0; %macro etls_recordCheck; %let etls_recCheckExist = %eval(%sysfunc(exist(ShLog.SatScanStatus_AED, DATA)) or %sysfunc(exist(ShLog.SatScanStatus_AED, VIEW))); %if (&etls_recCheckExist) %then %do; proc sql noprint; select count(*) into :etls_recnt from ShLog.SatScanStatus_AED; quit; %end; %mend etls_recordCheck; %etls_recordCheck; %let SYSLAST = %nrquote(ShLog.SatScanStatus_AED); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W16944G; quit; proc sql; create view work.W16944G as select SatScanStatus_AED.attendanceDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'AttendanceDate', SatScanStatus_AED.syndromeGroupID length = 8 format = 11. informat = 11. label = 'syndromeGroupID' from ShLog.SatScanStatus_AED as SatScanStatus_AED where SatScanStatus_AED.complete = 1 ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Join **/ /*==========================================================================* * Step: Extract A5NBTOIY.BY0002HS * * Transform: Extract * * Description: * * * * Source Table: CalculatedResult_AED - A5NBTOIY.BD0000BO * * Sherlock.CalculatedResult_AED * * Target Table: Extract - work.W1B0AGV A5NBTOIY.C60001N0 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HS); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); /* Access the data for Sherlock */ LIBNAME Sherlock ODBC DATASRC=sherlock_chp SCHEMA=dbo AUTHDOMAIN="sherlCHPAuth" ; %rcSet(&syslibrc); %let etls_recnt = 0; %macro etls_recordCheck; %let etls_recCheckExist = %eval(%sysfunc(exist(Sherlock.CalculatedResult_AED, DATA)) or %sysfunc(exist(Sherlock.CalculatedResult_AED, VIEW))); %if (&etls_recCheckExist) %then %do; proc sql noprint; select count(*) into :etls_recnt from Sherlock.CalculatedResult_AED; quit; %end; %mend etls_recordCheck; %etls_recordCheck; %let SYSLAST = %nrquote(Sherlock.CalculatedResult_AED); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; /*---- Map the columns ----*/ proc datasets lib = work nolist nowarn memtype = (data view); delete W1B0AGV; quit; data _null_; put "NOTE: The following column(s) do not have a column mapping, so the" " value(s) will be set to missing: labGroupID"; run; %put %str(NOTE: Mapping columns ...); proc sql; create view work.W1B0AGV as select attendanceDate, spatialLevelID, groupedCases, dailyCusum format = 11.6 informat = 11.6, dailyEWMACusumSignal, dailyCumulativePeriod, dailyPrediction format = 11.6 informat = 11.6, sevenDayMARate format = 11.6 informat = 11.6, ccSignal, resultType, measurementUnit, year, weekNum, weekEnding, month, syndromeGroupID, sevenDayMACount format = 14.8 informat = 14.8, controlChartUCL1, controlChartUCL2, controlChartUCLMR, EWMAUCL1, EWMAUCL2, HLM, controlChartUCL1Value format = 14.8 informat = 14.8, controlChartUCL2Value format = 14.8 informat = 14.8, controlChartUCLMRValue format = 14.8 informat = 14.8, EWMAPredicationZt format = 14.8 informat = 14.8, EWMAResidualet format = 14.8 informat = 14.8, EWMAUCL1Value format = 14.8 informat = 14.8, EWMAUCL2Value format = 14.8 informat = 14.8, HLM4WkTotal format = 14.8 informat = 14.8, HLMMean format = 14.8 informat = 14.8, HLMSD format = 14.8 informat = 14.8, HLMUCL format = 14.8 informat = 14.8, rate format = 11.6 informat = 11.6, . as labGroupID length = 8 format = 11. informat = 11. label = 'labGroupID' from &SYSLAST where year(attendanceDate)>=input(substr("&SNAPSHOT_ID",1,4),BEST.)-2 and year(attendanceDate)<=input(substr("&SNAPSHOT_ID",1,4),BEST.) and spatialLevelID="2409" and resultType=3695 and measurementUnit=2 ; quit; %let SYSLAST = work.W1B0AGV; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Extract **/ /*==========================================================================* * Step: Extract A5NBTOIY.BY0002HT * * Transform: Extract * * Description: * * * * Source Table: SyndromeGroup_AED - A5NBTOIY.BD0000F0 * * Sherlock.SyndromeGroup_AED * * Target Table: Extract - work.W1BUEVT A5NBTOIY.C60001N1 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HT); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = 0; %macro etls_recordCheck; %let etls_recCheckExist = %eval(%sysfunc(exist(Sherlock.SyndromeGroup_AED, DATA)) or %sysfunc(exist(Sherlock.SyndromeGroup_AED, VIEW))); %if (&etls_recCheckExist) %then %do; proc sql noprint; select count(*) into :etls_recnt from Sherlock.SyndromeGroup_AED; quit; %end; %mend etls_recordCheck; %etls_recordCheck; %let SYSLAST = %nrquote(Sherlock.SyndromeGroup_AED); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; /*---- Map the columns ----*/ proc datasets lib = work nolist nowarn memtype = (data view); delete W1BUEVT; quit; %put %str(NOTE: Mapping columns ...); proc sql; create view work.W1BUEVT as select syndromeGroupID, syndromeGroupName, syndromeGroupFullName, effectiveDate, expiryDate, displaySequence, remark, createdBy, createdTime, lastUpdatedBy, lastUpdatedTime from &SYSLAST where syndromeGroupID=2 and datepart(effectiveDate)<=input("&SNAPSHOT_ID",YYMMDD8.) and (datepart(expiryDate)>=input("&SNAPSHOT_ID",YYMMDD8.) or missing(expiryDate)) ; quit; %let SYSLAST = work.W1BUEVT; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Extract **/ /*==========================================================================* * Step: Join A5NBTOIY.BY0002HU * * Transform: Join * * Description: * * * * Source Tables: Extract - work.W1BUEVT A5NBTOIY.C60001N1 * * Extract - work.W1B0AGV A5NBTOIY.C60001N0 * * Target Table: Join - work.W1G5OJK A5NBTOIY.C60001N2 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HU); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = -1; /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W1G5OJK; quit; proc sql; create view work.W1G5OJK as select W1B0AGV.attendanceDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'attendanceDate', W1B0AGV.spatialLevelID length = 50 format = $50. informat = $50. label = 'spatialLevelID', W1B0AGV.groupedCases length = 8 format = 11. informat = 11. label = 'groupedCases', W1B0AGV.dailyCusum length = 8 format = 11.6 informat = 11.6 label = 'dailyCusum', W1B0AGV.dailyEWMACusumSignal length = 8 format = 1. informat = 1. label = 'dailyEWMACusumSignal', W1B0AGV.dailyCumulativePeriod length = 8 format = 6. informat = 6. label = 'dailyCumulativePeriod', W1B0AGV.dailyPrediction length = 8 format = 11.6 informat = 11.6 label = 'dailyPrediction', W1B0AGV.sevenDayMARate length = 8 format = 11.6 informat = 11.6 label = 'sevenDayMARate', W1B0AGV.ccSignal length = 8 format = 1. informat = 1. label = 'ccSignal', W1B0AGV.resultType length = 8 format = 11. informat = 11. label = 'resultType', W1B0AGV.measurementUnit length = 8 format = 4. informat = 4. label = 'measurementUnit', W1B0AGV.year length = 8 format = 6. informat = 6. label = 'year', W1B0AGV.weekNum length = 8 format = 4. informat = 4. label = 'weekNum', W1B0AGV.weekEnding length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'weekEnding', W1B0AGV.month length = 8 format = 4. informat = 4. label = 'month', W1B0AGV.syndromeGroupID length = 8 format = 11. informat = 11. label = 'syndromeGroupID', W1B0AGV.sevenDayMACount length = 8 format = 14.8 informat = 14.8 label = 'sevenDayMACount', W1B0AGV.controlChartUCL1 length = 8 format = 1. informat = 1. label = 'controlChartUCL1', W1B0AGV.controlChartUCL2 length = 8 format = 1. informat = 1. label = 'controlChartUCL2', W1B0AGV.controlChartUCLMR length = 8 format = 1. informat = 1. label = 'controlChartUCLMR', W1B0AGV.EWMAUCL1 length = 8 format = 1. informat = 1. label = 'EWMAUCL1', W1B0AGV.EWMAUCL2 length = 8 format = 1. informat = 1. label = 'EWMAUCL2', W1B0AGV.HLM length = 8 format = 1. informat = 1. label = 'HLM', W1B0AGV.controlChartUCL1Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL1Value', W1B0AGV.controlChartUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL2Value', W1B0AGV.controlChartUCLMRValue length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCLMRValue', W1B0AGV.EWMAPredicationZt length = 8 format = 14.8 informat = 14.8 label = 'EWMAPredicationZt', W1B0AGV.EWMAResidualet length = 8 format = 14.8 informat = 14.8 label = 'EWMAResidualet', W1B0AGV.EWMAUCL1Value length = 8 format = 14.8 informat = 14.8 label = 'EWMAUCL1Value', W1B0AGV.EWMAUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'EWMAUCL2Value', W1B0AGV.HLM4WkTotal length = 8 format = 14.8 informat = 14.8 label = 'HLM4WkTotal', W1B0AGV.HLMMean length = 8 format = 14.8 informat = 14.8 label = 'HLMMean', W1B0AGV.HLMSD length = 8 format = 14.8 informat = 14.8 label = 'HLMSD', W1B0AGV.HLMUCL length = 8 format = 14.8 informat = 14.8 label = 'HLMUCL', W1B0AGV.rate length = 8 format = 11.6 informat = 11.6 label = 'rate', W1B0AGV.labGroupID length = 8 format = 11. informat = 11. label = 'labGroupID', W1BUEVT.syndromeGroupName length = 50 format = $50. informat = $50. label = 'syndromeGroupName', W1BUEVT.syndromeGroupFullName length = 255 format = $255. informat = $255. label = 'syndromeGroupFullName', W1BUEVT.effectiveDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'effectiveDate', W1BUEVT.expiryDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'expiryDate', W1BUEVT.displaySequence length = 8 format = 6. informat = 6. label = 'displaySequence', W1BUEVT.remark length = 500 format = $500. informat = $500. label = 'remark', W1BUEVT.createdBy length = 20 format = $20. informat = $20. label = 'createdBy', W1BUEVT.createdTime length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'createdTime', W1BUEVT.lastUpdatedBy length = 20 format = $20. informat = $20. label = 'lastUpdatedBy', W1BUEVT.lastUpdatedTime length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'lastUpdatedTime' from work.W1B0AGV as W1B0AGV, work.W1BUEVT as W1BUEVT where W1B0AGV.syndromeGroupID = W1BUEVT.syndromeGroupID ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Join **/ /*==========================================================================* * Step: Join A5NBTOIY.BY0002HV * * Transform: Join * * Description: * * * * Source Tables: SnapshotSatScanResult_AED - A5NBTOIY.BD0000EJ * * Sherlock.SnapshotSatScanResult_AED * * Extract - work.W1B0AGV A5NBTOIY.C60001N0 * * Target Table: Join - work.W1GAR6L A5NBTOIY.C60001N3 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HV); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = 0; %macro etls_recordCheck; %let etls_recCheckExist = %eval(%sysfunc(exist(Sherlock.SnapshotSatScanResult_AED, DATA)) or %sysfunc(exist(Sherlock.SnapshotSatScanResult_AED, VIEW))); %if (&etls_recCheckExist) %then %do; proc sql noprint; select count(*) into :etls_recnt from Sherlock.SnapshotSatScanResult_AED; quit; %end; %mend etls_recordCheck; %etls_recordCheck; /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W1GAR6L; quit; proc sql; create view work.W1GAR6L as select SnapshotSatScanResult_AED.syndromeGroupID length = 8 format = 11. informat = 11. label = 'syndromeGroupID', SnapshotSatScanResult_AED.attendanceDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'attendanceDate' from Sherlock.SnapshotSatScanResult_AED as SnapshotSatScanResult_AED, work.W1B0AGV as W1B0AGV where SnapshotSatScanResult_AED.syndromeGroupID = W1B0AGV.syndromeGroupID and SnapshotSatScanResult_AED.attendanceDate = W1B0AGV.attendanceDate and SnapshotSatScanResult_AED.syndromeGroupID NOT IS MISSING ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Join **/ /*==========================================================================* * Step: Join A5NBTOIY.BY0002HW * * Transform: Join * * Description: * * * * Source Tables: Join - work.W1GAR6L A5NBTOIY.C60001N3 * * Join - work.W16944G A5NBTOIY.C60001MZ * * Target Table: Join - work.W1BBHKG A5NBTOIY.C60001N4 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HW); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = -1; /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W1BBHKG; quit; proc sql; create view work.W1BBHKG as select W16944G.AttendanceDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'AttendanceDate', W16944G.syndromeGroupID length = 8 format = 11. informat = 11. label = 'syndromeGroupID', count(W1GAR6L.attendanceDate) as count length = 8 from work.W16944G as W16944G left join work.W1GAR6L as W1GAR6L on ( W16944G.AttendanceDate = W1GAR6L.attendanceDate and W16944G.syndromeGroupID = W1GAR6L.syndromeGroupID ) ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Join **/ /*==========================================================================* * Step: Join A5NBTOIY.BY0002HX * * Transform: Join * * Description: * * * * Source Tables: Join - work.W1G5OJK A5NBTOIY.C60001N2 * * Join - work.W1BBHKG A5NBTOIY.C60001N4 * * Extract - work.W1AQIJO A5NBTOIY.C60001MX * * Target Table: Join - work.W1GMBDR A5NBTOIY.C60001N5 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HX); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = -1; /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W1GMBDR; quit; proc sql; create view work.W1GMBDR as select W1AQIJO.odate length = 8 format = DATE9. informat = DATE9., W1G5OJK.attendanceDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'attendanceDate', W1G5OJK.spatialLevelID length = 50 format = $50. informat = $50. label = 'spatialLevelID', W1G5OJK.groupedCases length = 8 format = 11. informat = 11. label = 'groupedCases', W1G5OJK.dailyCusum length = 8 format = 11.6 informat = 11.6 label = 'dailyCusum', W1G5OJK.dailyEWMACusumSignal length = 8 format = 1. informat = 1. label = 'dailyEWMACusumSignal', W1G5OJK.dailyCumulativePeriod length = 8 format = 6. informat = 6. label = 'dailyCumulativePeriod', W1G5OJK.dailyPrediction length = 8 format = 11.6 informat = 11.6 label = 'dailyPrediction', W1G5OJK.sevenDayMARate length = 8 format = 11.6 informat = 11.6 label = 'sevenDayMARate', W1G5OJK.ccSignal length = 8 format = 1. informat = 1. label = 'ccSignal', W1G5OJK.resultType length = 8 format = 11. informat = 11. label = 'resultType', W1G5OJK.measurementUnit length = 8 format = 4. informat = 4. label = 'measurementUnit', W1G5OJK.year length = 8 format = 6. informat = 6. label = 'year', W1G5OJK.weekNum length = 8 format = 4. informat = 4. label = 'weekNum', W1G5OJK.weekEnding length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'weekEnding', W1G5OJK.month length = 8 format = 4. informat = 4. label = 'month', W1G5OJK.syndromeGroupID length = 8 format = 11. informat = 11. label = 'syndromeGroupID', W1G5OJK.sevenDayMACount length = 8 format = 14.8 informat = 14.8 label = 'sevenDayMACount', W1G5OJK.controlChartUCL1 length = 8 format = 1. informat = 1. label = 'controlChartUCL1', W1G5OJK.controlChartUCL2 length = 8 format = 1. informat = 1. label = 'controlChartUCL2', W1G5OJK.controlChartUCLMR length = 8 format = 1. informat = 1. label = 'controlChartUCLMR', W1G5OJK.EWMAUCL1 length = 8 format = 1. informat = 1. label = 'EWMAUCL1', W1G5OJK.EWMAUCL2 length = 8 format = 1. informat = 1. label = 'EWMAUCL2', W1G5OJK.HLM length = 8 format = 1. informat = 1. label = 'HLM', W1G5OJK.controlChartUCL1Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL1Value', W1G5OJK.controlChartUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL2Value', W1G5OJK.controlChartUCLMRValue length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCLMRValue', W1G5OJK.EWMAPredicationZt length = 8 format = 14.8 informat = 14.8 label = 'EWMAPredicationZt', W1G5OJK.EWMAResidualet length = 8 format = 14.8 informat = 14.8 label = 'EWMAResidualet', W1G5OJK.EWMAUCL1Value length = 8 format = 14.8 informat = 14.8 label = 'EWMAUCL1Value', W1G5OJK.EWMAUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'EWMAUCL2Value', W1G5OJK.HLM4WkTotal length = 8 format = 14.8 informat = 14.8 label = 'HLM4WkTotal', W1G5OJK.HLMMean length = 8 format = 14.8 informat = 14.8 label = 'HLMMean', W1G5OJK.HLMSD length = 8 format = 14.8 informat = 14.8 label = 'HLMSD', W1G5OJK.HLMUCL length = 8 format = 14.8 informat = 14.8 label = 'HLMUCL', W1G5OJK.rate length = 8 format = 11.6 informat = 11.6 label = 'rate', W1G5OJK.labGroupID length = 8 format = 11. informat = 11. label = 'labGroupID', W1G5OJK.syndromeGroupName length = 50 format = $50. informat = $50. label = 'syndromeGroupName', W1G5OJK.syndromeGroupFullName length = 255 format = $255. informat = $255. label = 'syndromeGroupFullName', W1G5OJK.effectiveDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'effectiveDate', W1G5OJK.expiryDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'expiryDate', W1G5OJK.displaySequence length = 8 format = 6. informat = 6. label = 'displaySequence', W1G5OJK.remark length = 500 format = $500. informat = $500. label = 'remark', W1G5OJK.createdBy length = 20 format = $20. informat = $20. label = 'createdBy', W1G5OJK.createdTime length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'createdTime', W1G5OJK.lastUpdatedBy length = 20 format = $20. informat = $20. label = 'lastUpdatedBy', W1G5OJK.lastUpdatedTime length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'lastUpdatedTime', W1BBHKG.count length = 8 from work.W1AQIJO as W1AQIJO left join work.W1G5OJK as W1G5OJK on ( W1AQIJO.odate = datepart(W1G5OJK.attendanceDate) ) left join work.W1BBHKG as W1BBHKG on ( W1G5OJK.attendanceDate = W1BBHKG.AttendanceDate ) ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Join **/ /*==========================================================================* * Step: Create Table A5NBTOIY.BY0002HY * * Transform: Create Table * * Description: * * * * Source Table: Join - work.W1GMBDR A5NBTOIY.C60001N5 * * Target Table: Create Table - work.W1GY0QH A5NBTOIY.C60001N6 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HY); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = -1; %let SYSLAST = %nrquote(work.W1GMBDR); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLCreateTable), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W1GY0QH; quit; proc sql ; create view work.W1GY0QH as select W1GMBDR.odate length = 8 format = DATE9. informat = DATE9., case when W1GMBDR.dailyEWMACusumSignal = 1 then W1GMBDR.rate end as dailyEWMACusumSignal length = 8 format = 11.6 informat = 11.6 label = 'dailyEWMACusumSignal', W1GMBDR.sevenDayMARate as sevenDayMARate length = 8 format = 11.6 informat = 11.6 label = 'sevenDayMARate', W1GMBDR.ccSignal length = 8 format = 1. informat = 1. label = 'ccSignal', W1GMBDR.controlChartUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL2Value', W1GMBDR.syndromeGroupFullName length = 255 format = $255. informat = $255. label = 'syndromeGroupFullName', W1GMBDR.count length = 8, W1GMBDR.rate as rate length = 8 format = 11.6 informat = 11.6 label = 'rate' from work.W1GMBDR as W1GMBDR ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Create Table **/ /*==========================================================================* * Step: Delete A5NBTOIY.BY0002HZ * * Transform: Delete * * Description: * * * * Target Table: RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL * * RPTIMAGE.RST_DAT_BGIS_108_001 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HZ); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); /* Access the data for RptImage */ LIBNAME RPTIMAGE BASE "\\dcdissas54v\sas\CDIS\ReportSnapshot"; %rcSet(&syslibrc); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SqlDelete), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; %macro etls_sql_delete; /* Determine if the table exists */ %let etls_tableExist = %eval(%sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, DATA)) or %sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, VIEW))); /*---- Create a new table ----*/ %if (&etls_tableExist eq 0) %then %do; /* if table does not exist */ %put %str(NOTE: Creating table ...); data RPTIMAGE.RST_DAT_BGIS_108_001; attrib year length = 8; attrib month length = 8; attrib day length = 8; attrib odate length = 8 format = DATE9. informat = DATE9.; attrib Adm_date length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'Adm_date'; attrib syndromeGroupFullName length = $255 format = $255. informat = $255. label = 'syndromeGroupFullName'; attrib sevenDayMARate length = 8 format = 11.6 informat = 11.6 label = 'sevenDayMARate'; attrib rate length = 8 format = 11.6 informat = 11.6 label = 'rate'; attrib controlChartUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL2Value'; attrib dailyEWMACusumSignal length = 8 format = 11.6 informat = 11.6 label = 'dailyEWMACusumSignal'; attrib ccSignal length = 8 format = 1. informat = 1. label = 'ccSignal'; attrib count length = 8; attrib snapshot_id length = $8; attrib day2 length = 8; call missing(of _all_); stop; run; %rcSet(&syserr); %end; /* if table does not exist */ /*---- Delete the target table ----*/ proc sql; delete from RPTIMAGE.RST_DAT_BGIS_108_001 where RST_DAT_BGIS_108_001.snapshot_id = "&snapshot_id" ;quit; %rcSet(&syscc); %mend etls_sql_delete; /* execute etls_sql_delete */ %etls_sql_delete %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Delete **/ /*==========================================================================* * Step: Insert Rows A5NBTOIY.BY0002I0 * * Transform: Insert Rows * * Description: * * * * Source Table: Create Table - work.W1GY0QH A5NBTOIY.C60001N6 * * Target Table: RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL * * RPTIMAGE.RST_DAT_BGIS_108_001 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002I0); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = -1; %let SYSLAST = %nrquote(work.W1GY0QH); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLInsertRows), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; %macro etls_sql_insert_rows; /* Determine if the table exists */ %let etls_tableExist = %eval(%sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, DATA)) or %sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, VIEW))); /*---- Create a new table ----*/ %if (&etls_tableExist eq 0) %then %do; /* if table does not exist */ %put %str(NOTE: Creating table ...); data RPTIMAGE.RST_DAT_BGIS_108_001; attrib year length = 8; attrib month length = 8; attrib day length = 8; attrib odate length = 8 format = DATE9. informat = DATE9.; attrib Adm_date length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'Adm_date'; attrib syndromeGroupFullName length = $255 format = $255. informat = $255. label = 'syndromeGroupFullName'; attrib sevenDayMARate length = 8 format = 11.6 informat = 11.6 label = 'sevenDayMARate'; attrib rate length = 8 format = 11.6 informat = 11.6 label = 'rate'; attrib controlChartUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL2Value'; attrib dailyEWMACusumSignal length = 8 format = 11.6 informat = 11.6 label = 'dailyEWMACusumSignal'; attrib ccSignal length = 8 format = 1. informat = 1. label = 'ccSignal'; attrib count length = 8; attrib snapshot_id length = $8; attrib day2 length = 8; call missing(of _all_); stop; run; %rcSet(&syserr); %end; /* if table does not exist */ /*---- Insert rows into target table ----*/ proc sql; insert into RPTIMAGE.RST_DAT_BGIS_108_001 (year, month, day, odate, Adm_date, syndromeGroupFullName, sevenDayMARate, rate, controlChartUCL2Value, dailyEWMACusumSignal, ccSignal, count, snapshot_id, day2) select distinct year(W1GY0QH.odate) as year length = 8, month(W1GY0QH.odate) as month length = 8, day(W1GY0QH.odate) as day length = 8, W1GY0QH.odate length = 8 format = DATE9. informat = DATE9., . as Adm_date length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'Adm_date', W1GY0QH.syndromeGroupFullName length = 255 format = $255. informat = $255. label = 'syndromeGroupFullName', W1GY0QH.sevenDayMARate length = 8 format = 11.6 informat = 11.6 label = 'sevenDayMARate', W1GY0QH.rate length = 8 format = 11.6 informat = 11.6 label = 'rate', W1GY0QH.controlChartUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL2Value', W1GY0QH.dailyEWMACusumSignal length = 8 format = 11.6 informat = 11.6 label = 'dailyEWMACusumSignal', W1GY0QH.ccSignal length = 8 format = 1. informat = 1. label = 'ccSignal', W1GY0QH.count length = 8, "&snapshot_id" as snapshot_id length = 8, . as day2 length = 8 from work.W1GY0QH as W1GY0QH ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %mend etls_sql_insert_rows; /* execute etls_sql_insert_rows */ %etls_sql_insert_rows; /*---- Start of Post-Process Code ----*/ data RPTIMAGE.RST_DAT_BGIS_108_001; set RPTIMAGE.RST_DAT_BGIS_108_001; if count=0 then count=.; else count=count; run; /*---- End of Post-Process Code ----*/ %rcSet(&syserr); %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Insert Rows **/ /*---- Start of Post-Process Code ----*/ %let log_endTime = %sysfunc(datetime(),datetime21.2); proc sql; insert into BGIS.DIS_LOG (DIS_JOB_ID, LAST_SNAPSHOT_ID, RUN_DATE, REMARK) values ("&etls_jobName", "&snapshot_id", "&log_endTime"dt, ""); run; /*---- End of Post-Process Code ----*/ %rcSet(&syserr); %rcSet(&sqlrc); %let etls_endTime = %sysfunc(datetime(),datetime.); /* Turn off performance statistics collection */ data _null_; if "&_perfinit" eq "1" then call execute('%perfend;'); run; /** Job end DIS-JOB-BGIS-108-001 **/ /* Reset jobid and name */ %let jobID = %quote(A5NBTOIY.BV0004VJ); %let etls_jobName = %nrquote(A_testing); /*==========================================================================* * Step: Set Flag to N A5NBTOIY.BY000BIW * * Transform: User Written * * Description: * * * * Target Table: User Written - work.WI1GVQG A5NBTOIY.C600087Q * * * * User Written: SourceCode * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY000BIW); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|UserWritten), metrNam6=_DISROWCNT, metrDef6=Count32) ; %let _INPUT_count = 0; %let _OUTPUT_count = 1; %let _OUTPUT = work.WI1GVQG; %let _OUTPUT_connect = ; %let _OUTPUT_engine = ; %let _OUTPUT_memtype = DATA; %let _OUTPUT_options = %nrquote(); %let _OUTPUT_alter = %nrquote(); %let _OUTPUT_path = %nrquote(/User Written_A5NBTOIY.C600087Q%(WorkTable%)); %let _OUTPUT_type = 1; %let _OUTPUT_label = %nrquote(); %let _OUTPUT_col_count = 0; %let _OUTPUT1 = work.WI1GVQG; %let _OUTPUT1_connect = ; %let _OUTPUT1_engine = ; %let _OUTPUT1_memtype = DATA; %let _OUTPUT1_options = %nrquote(); %let _OUTPUT1_alter = %nrquote(); %let _OUTPUT1_path = %nrquote(/User Written_A5NBTOIY.C600087Q%(WorkTable%)); %let _OUTPUT1_type = 1; %let _OUTPUT1_label = %nrquote(); %let _OUTPUT1_col_count = 0; /*---- Start of User Written Code ----*/ data SOL.REPORT_TO_DI_MAPPING; set SOL.REPORT_TO_DI_MAPPING; if REPORT='RPT-BGIS-022W' then FLAG='N'; run; /*---- End of User Written Code ----*/ %rcSet(&syserr); %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Set Flag to N **/ %exitetls_conditionWH2ICMT: %mend etls_conditionWH2ICMT; %etls_conditionWH2ICMT; /** Step end Conditional Start **/ /*==========================================================================* * Step: Delete A5NBTOIY.BY000BIX * * Transform: Delete * * Description: * * * * Target Table: RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL * * RPTIMAGE.RST_DAT_BGIS_108_001 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY000BIX); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SqlDelete), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; %macro etls_sql_delete; /* Determine if the table exists */ %let etls_tableExist = %eval(%sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, DATA)) or %sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, VIEW))); /*---- Create a new table ----*/ %if (&etls_tableExist eq 0) %then %do; /* if table does not exist */ %put %str(NOTE: Creating table ...); data RPTIMAGE.RST_DAT_BGIS_108_001; attrib year length = 8; attrib month length = 8; attrib day length = 8; attrib odate length = 8 format = DATE9. informat = DATE9.; attrib Adm_date length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'Adm_date'; attrib syndromeGroupFullName length = $255 format = $255. informat = $255. label = 'syndromeGroupFullName'; attrib sevenDayMARate length = 8 format = 11.6 informat = 11.6 label = 'sevenDayMARate'; attrib rate length = 8 format = 11.6 informat = 11.6 label = 'rate'; attrib controlChartUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL2Value'; attrib dailyEWMACusumSignal length = 8 format = 11.6 informat = 11.6 label = 'dailyEWMACusumSignal'; attrib ccSignal length = 8 format = 1. informat = 1. label = 'ccSignal'; attrib count length = 8; attrib snapshot_id length = $8; attrib day2 length = 8; call missing(of _all_); stop; run; %rcSet(&syserr); %end; /* if table does not exist */ /*---- Delete the target table ----*/ proc sql; delete from RPTIMAGE.RST_DAT_BGIS_108_001 where RST_DAT_BGIS_108_001.snapshot_id = '20171231' ;quit; %rcSet(&syscc); %mend etls_sql_delete; /* execute etls_sql_delete */ %etls_sql_delete %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Delete **/ %let etls_endTime = %sysfunc(datetime(),datetime.); /* Turn off performance statistics collection */ data _null_; if "&_perfinit" eq "1" then call execute('%perfend;'); run; 2) DIS-JOB-BGIS-108-001 Code: /**************************************************************************** * Job: DIS-JOB-BGIS-108-001 A5NBTOIY.BV00004F * * Description: * * * * Metadata Server: dcdissas53v.cdisdev.chp.hksarg * * Port: 8561 * * Location: /DIS/Jobs * * * * Server: SASApp A5NBTOIY.AS000003 * * * * Source Tables: DIS_LOG - BGIS.DIS_LOG A5NBTOIY.BD00010K * * WEEK_NUMBER - SOL.WEEK_NUMBER A5NBTOIY.BD00000A * * CalculatedResult_AED - A5NBTOIY.BD0000BO * * Sherlock.CalculatedResult_AED * * SyndromeGroup_AED - A5NBTOIY.BD0000F0 * * Sherlock.SyndromeGroup_AED * * SnapshotSatScanResult_AED - A5NBTOIY.BD0000EJ * * Sherlock.SnapshotSatScanResult_AED * * SatScanStatus_AED - A5NBTOIY.BD000434 * * ShLog.SatScanStatus_AED * * Target Tables: RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL * * RPTIMAGE.RST_DAT_BGIS_108_001 * * RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL * * RPTIMAGE.RST_DAT_BGIS_108_001 * * * * Generated on: 2015年03月23日 星期一 下午04時43分34秒 CST * * Generated by: super2@CDISDEV * * Version: SAS Data Integration Studio 4.9 * ****************************************************************************/ /* Generate the process id for job */ %put Process ID: &SYSJOBID; /* General macro variables */ %let jobID = %quote(A5NBTOIY.BV00004F); %let etls_jobName = %nrquote(DIS-JOB-BGIS-108-001); %let etls_userID = %nrquote(super2@CDISDEV); %global applName; data _null_; applName="SAS Data Integration Studio"; call symput('applName',%nrstr(applName)); run; /* Performance Statistics require ARM_PROC sub-system */ %macro etls_startPerformanceStats; %log4sas(); %log4sas_logger(Perf.ARM, 'level=info'); options armagent=log4sas armsubsys=(ARM_PROC); %global _armexec; %let _armexec = 1; %perfinit(applname="&applName"); %global etls_recnt; %let etls_recnt=-1; %mend; %etls_startPerformanceStats; %macro etls_setArmagent; %let armagentLength = %length(%sysfunc(getoption(armagent))); %if (&armagentLength eq 0) %then %do; %log4sas(); %log4sas_logger(Perf.ARM, 'level=info'); options armagent=log4sas armsubsys=(ARM_PROC); %end; %mend etls_setArmagent; %macro etls_setPerfInit; %if "&_perfinit" eq "0" %then %do; %etls_setArmagent; %global _armexec; %let _armexec = 1; %perfinit(applname="&applName"); %end; %mend etls_setPerfInit; /* Setup to capture return codes */ %global job_rc trans_rc sqlrc syscc; %let sysrc = 0; %let job_rc = 0; %let trans_rc = 0; %let sqlrc = 0; %let syscc = 0; %global etls_stepStartTime; /* initialize syserr to 0 */ data _null_; run; %macro rcSet(error); %if (&error gt &trans_rc) %then %let trans_rc = &error; %if (&error gt &job_rc) %then %let job_rc = &error; %mend rcSet; %macro rcSetDS(error); if &error gt input(symget('trans_rc'),12.) then call symput('trans_rc',trim(left(put(&error,12.)))); if &error gt input(symget('job_rc'),12.) then call symput('job_rc',trim(left(put(&error,12.)))); %mend rcSetDS; /* Create metadata macro variables */ %let IOMServer = %nrquote(SASApp); %let metaPort = %nrquote(8561); %let metaServer = %nrquote(dcdissas53v.cdisdev.chp.hksarg); /* Set metadata options */ options metaport = &metaPort metaserver = "&metaServer"; /* Setup for capturing job status */ %let etls_startTime = %sysfunc(datetime(),datetime.); %let etls_recordsBefore = 0; %let etls_recordsAfter = 0; %let etls_lib = 0; %let etls_table = 0; %global etls_debug; %macro etls_setDebug; %if %str(&etls_debug) ne 0 %then OPTIONS MPRINT%str(;); %mend; %etls_setDebug; /*---- Start of Pre-Process Code ----*/ *%LET snapshot_id=20131231; %put &snapshot_id.; /*---- End of Pre-Process Code ----*/ %rcSet(&syserr); %rcSet(&sqlrc); /*==========================================================================* * Step: Extract A5NBTOIY.BY0002HO * * Transform: Extract * * Description: * * * * Source Table: DIS_LOG - BGIS.DIS_LOG A5NBTOIY.BD00010K * * Target Table: Extract - work.W1ALZ89 A5NBTOIY.C60001MW * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HO); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); /* Access the data for CDIS_BGIS */ LIBNAME BGIS ORACLE DBSERVER_MAX_BYTES=1 DBCLIENT_MAX_BYTES=1 PATH=ANASIT0 SCHEMA=CDIS_BGIS AUTHDOMAIN="bgisdbAuth" ; %rcSet(&syslibrc); %let etls_recnt = 0; %macro etls_recordCheck; %let etls_recCheckExist = %eval(%sysfunc(exist(BGIS.DIS_LOG, DATA)) or %sysfunc(exist(BGIS.DIS_LOG, VIEW))); %if (&etls_recCheckExist) %then %do; proc sql noprint; select count(*) into :etls_recnt from BGIS.DIS_LOG; quit; %end; %mend etls_recordCheck; %etls_recordCheck; %let SYSLAST = %nrquote(BGIS.DIS_LOG); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; /*---- Map the columns ----*/ proc datasets lib = work nolist nowarn memtype = (data view); delete W1ALZ89; quit; %put %str(NOTE: Mapping columns ...); proc sql; create view work.W1ALZ89 as select DIS_JOB_ID, LAST_SNAPSHOT_ID, RUN_DATE, REMARK from &SYSLAST ; quit; %let SYSLAST = work.W1ALZ89; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Extract **/ /*==========================================================================* * Step: Extract A5NBTOIY.BY0002HP * * Transform: Extract * * Description: * * * * Source Table: WEEK_NUMBER - SOL.WEEK_NUMBER A5NBTOIY.BD00000A * * Target Table: Extract - work.W1AQIJO A5NBTOIY.C60001MX * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HP); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); /* Access the data for SOLITARY */ LIBNAME SOL BASE "\\dcdissas54v\sas\CDIS\Solitary File"; %rcSet(&syslibrc); %let etls_recCheckExist = 0; %let etls_recnt = 0; %macro etls_recordCheck; %let etls_recCheckExist = %eval(%sysfunc(exist(SOL.WEEK_NUMBER, DATA)) or %sysfunc(exist(SOL.WEEK_NUMBER, VIEW))); %if (&etls_recCheckExist) %then %do; %local etls_syntaxcheck; %let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck)); /* Turn off syntaxcheck option to perform following steps */ options nosyntaxcheck; proc contents data = SOL.WEEK_NUMBER out = work.etls_contents(keep = nobs) noprint; run; data _null_; set work.etls_contents (obs = 1); call symput("etls_recnt", left(put(nobs,32.))); run; proc datasets lib = work nolist nowarn memtype = (data view); delete etls_contents; quit; /* Reset syntaxcheck option to previous setting */ options &etls_syntaxcheck; %end; %mend etls_recordCheck; %etls_recordCheck; %let SYSLAST = %nrquote(SOL.WEEK_NUMBER); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; /*---- Map the columns ----*/ proc datasets lib = work nolist nowarn memtype = (data view); delete W1AQIJO; quit; %put %str(NOTE: Mapping columns ...); proc sql; create view work.W1AQIJO as select odate from &SYSLAST where year(ODATE)>=input(substr("&SNAPSHOT_ID",1,4),BEST.)-2 and ODATE<=input("&SNAPSHOT_ID",yymmdd8.)-1 ; quit; %let SYSLAST = work.W1AQIJO; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Extract **/ /*==========================================================================* * Step: Join A5NBTOIY.BY0002HR * * Transform: Join * * Description: * * * * Source Table: SatScanStatus_AED - A5NBTOIY.BD000434 * * ShLog.SatScanStatus_AED * * Target Table: Join - work.W16944G A5NBTOIY.C60001MZ * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HR); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); /* Access the data for Sherlock_Log */ LIBNAME ShLog ODBC DATASRC=sherlock_log_chp SCHEMA=dbo AUTHDOMAIN="SherlLogCHPAuth" ; %rcSet(&syslibrc); %let etls_recnt = 0; %macro etls_recordCheck; %let etls_recCheckExist = %eval(%sysfunc(exist(ShLog.SatScanStatus_AED, DATA)) or %sysfunc(exist(ShLog.SatScanStatus_AED, VIEW))); %if (&etls_recCheckExist) %then %do; proc sql noprint; select count(*) into :etls_recnt from ShLog.SatScanStatus_AED; quit; %end; %mend etls_recordCheck; %etls_recordCheck; %let SYSLAST = %nrquote(ShLog.SatScanStatus_AED); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W16944G; quit; proc sql; create view work.W16944G as select SatScanStatus_AED.attendanceDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'AttendanceDate', SatScanStatus_AED.syndromeGroupID length = 8 format = 11. informat = 11. label = 'syndromeGroupID' from ShLog.SatScanStatus_AED as SatScanStatus_AED where SatScanStatus_AED.complete = 1 ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Join **/ /*==========================================================================* * Step: Extract A5NBTOIY.BY0002HS * * Transform: Extract * * Description: * * * * Source Table: CalculatedResult_AED - A5NBTOIY.BD0000BO * * Sherlock.CalculatedResult_AED * * Target Table: Extract - work.W1B0AGV A5NBTOIY.C60001N0 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HS); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); /* Access the data for Sherlock */ LIBNAME Sherlock ODBC DATASRC=sherlock_chp SCHEMA=dbo AUTHDOMAIN="sherlCHPAuth" ; %rcSet(&syslibrc); %let etls_recnt = 0; %macro etls_recordCheck; %let etls_recCheckExist = %eval(%sysfunc(exist(Sherlock.CalculatedResult_AED, DATA)) or %sysfunc(exist(Sherlock.CalculatedResult_AED, VIEW))); %if (&etls_recCheckExist) %then %do; proc sql noprint; select count(*) into :etls_recnt from Sherlock.CalculatedResult_AED; quit; %end; %mend etls_recordCheck; %etls_recordCheck; %let SYSLAST = %nrquote(Sherlock.CalculatedResult_AED); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; /*---- Map the columns ----*/ proc datasets lib = work nolist nowarn memtype = (data view); delete W1B0AGV; quit; data _null_; put "NOTE: The following column(s) do not have a column mapping, so the" " value(s) will be set to missing: labGroupID"; run; %put %str(NOTE: Mapping columns ...); proc sql; create view work.W1B0AGV as select attendanceDate, spatialLevelID, groupedCases, dailyCusum format = 11.6 informat = 11.6, dailyEWMACusumSignal, dailyCumulativePeriod, dailyPrediction format = 11.6 informat = 11.6, sevenDayMARate format = 11.6 informat = 11.6, ccSignal, resultType, measurementUnit, year, weekNum, weekEnding, month, syndromeGroupID, sevenDayMACount format = 14.8 informat = 14.8, controlChartUCL1, controlChartUCL2, controlChartUCLMR, EWMAUCL1, EWMAUCL2, HLM, controlChartUCL1Value format = 14.8 informat = 14.8, controlChartUCL2Value format = 14.8 informat = 14.8, controlChartUCLMRValue format = 14.8 informat = 14.8, EWMAPredicationZt format = 14.8 informat = 14.8, EWMAResidualet format = 14.8 informat = 14.8, EWMAUCL1Value format = 14.8 informat = 14.8, EWMAUCL2Value format = 14.8 informat = 14.8, HLM4WkTotal format = 14.8 informat = 14.8, HLMMean format = 14.8 informat = 14.8, HLMSD format = 14.8 informat = 14.8, HLMUCL format = 14.8 informat = 14.8, rate format = 11.6 informat = 11.6, . as labGroupID length = 8 format = 11. informat = 11. label = 'labGroupID' from &SYSLAST where year(attendanceDate)>=input(substr("&SNAPSHOT_ID",1,4),BEST.)-2 and year(attendanceDate)<=input(substr("&SNAPSHOT_ID",1,4),BEST.) and spatialLevelID="2409" and resultType=3695 and measurementUnit=2 ; quit; %let SYSLAST = work.W1B0AGV; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Extract **/ /*==========================================================================* * Step: Extract A5NBTOIY.BY0002HT * * Transform: Extract * * Description: * * * * Source Table: SyndromeGroup_AED - A5NBTOIY.BD0000F0 * * Sherlock.SyndromeGroup_AED * * Target Table: Extract - work.W1BUEVT A5NBTOIY.C60001N1 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HT); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = 0; %macro etls_recordCheck; %let etls_recCheckExist = %eval(%sysfunc(exist(Sherlock.SyndromeGroup_AED, DATA)) or %sysfunc(exist(Sherlock.SyndromeGroup_AED, VIEW))); %if (&etls_recCheckExist) %then %do; proc sql noprint; select count(*) into :etls_recnt from Sherlock.SyndromeGroup_AED; quit; %end; %mend etls_recordCheck; %etls_recordCheck; %let SYSLAST = %nrquote(Sherlock.SyndromeGroup_AED); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; /*---- Map the columns ----*/ proc datasets lib = work nolist nowarn memtype = (data view); delete W1BUEVT; quit; %put %str(NOTE: Mapping columns ...); proc sql; create view work.W1BUEVT as select syndromeGroupID, syndromeGroupName, syndromeGroupFullName, effectiveDate, expiryDate, displaySequence, remark, createdBy, createdTime, lastUpdatedBy, lastUpdatedTime from &SYSLAST where syndromeGroupID=2 and datepart(effectiveDate)<=input("&SNAPSHOT_ID",YYMMDD8.) and (datepart(expiryDate)>=input("&SNAPSHOT_ID",YYMMDD8.) or missing(expiryDate)) ; quit; %let SYSLAST = work.W1BUEVT; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Extract **/ /*==========================================================================* * Step: Join A5NBTOIY.BY0002HU * * Transform: Join * * Description: * * * * Source Tables: Extract - work.W1BUEVT A5NBTOIY.C60001N1 * * Extract - work.W1B0AGV A5NBTOIY.C60001N0 * * Target Table: Join - work.W1G5OJK A5NBTOIY.C60001N2 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HU); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = -1; /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W1G5OJK; quit; proc sql; create view work.W1G5OJK as select W1B0AGV.attendanceDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'attendanceDate', W1B0AGV.spatialLevelID length = 50 format = $50. informat = $50. label = 'spatialLevelID', W1B0AGV.groupedCases length = 8 format = 11. informat = 11. label = 'groupedCases', W1B0AGV.dailyCusum length = 8 format = 11.6 informat = 11.6 label = 'dailyCusum', W1B0AGV.dailyEWMACusumSignal length = 8 format = 1. informat = 1. label = 'dailyEWMACusumSignal', W1B0AGV.dailyCumulativePeriod length = 8 format = 6. informat = 6. label = 'dailyCumulativePeriod', W1B0AGV.dailyPrediction length = 8 format = 11.6 informat = 11.6 label = 'dailyPrediction', W1B0AGV.sevenDayMARate length = 8 format = 11.6 informat = 11.6 label = 'sevenDayMARate', W1B0AGV.ccSignal length = 8 format = 1. informat = 1. label = 'ccSignal', W1B0AGV.resultType length = 8 format = 11. informat = 11. label = 'resultType', W1B0AGV.measurementUnit length = 8 format = 4. informat = 4. label = 'measurementUnit', W1B0AGV.year length = 8 format = 6. informat = 6. label = 'year', W1B0AGV.weekNum length = 8 format = 4. informat = 4. label = 'weekNum', W1B0AGV.weekEnding length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'weekEnding', W1B0AGV.month length = 8 format = 4. informat = 4. label = 'month', W1B0AGV.syndromeGroupID length = 8 format = 11. informat = 11. label = 'syndromeGroupID', W1B0AGV.sevenDayMACount length = 8 format = 14.8 informat = 14.8 label = 'sevenDayMACount', W1B0AGV.controlChartUCL1 length = 8 format = 1. informat = 1. label = 'controlChartUCL1', W1B0AGV.controlChartUCL2 length = 8 format = 1. informat = 1. label = 'controlChartUCL2', W1B0AGV.controlChartUCLMR length = 8 format = 1. informat = 1. label = 'controlChartUCLMR', W1B0AGV.EWMAUCL1 length = 8 format = 1. informat = 1. label = 'EWMAUCL1', W1B0AGV.EWMAUCL2 length = 8 format = 1. informat = 1. label = 'EWMAUCL2', W1B0AGV.HLM length = 8 format = 1. informat = 1. label = 'HLM', W1B0AGV.controlChartUCL1Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL1Value', W1B0AGV.controlChartUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL2Value', W1B0AGV.controlChartUCLMRValue length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCLMRValue', W1B0AGV.EWMAPredicationZt length = 8 format = 14.8 informat = 14.8 label = 'EWMAPredicationZt', W1B0AGV.EWMAResidualet length = 8 format = 14.8 informat = 14.8 label = 'EWMAResidualet', W1B0AGV.EWMAUCL1Value length = 8 format = 14.8 informat = 14.8 label = 'EWMAUCL1Value', W1B0AGV.EWMAUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'EWMAUCL2Value', W1B0AGV.HLM4WkTotal length = 8 format = 14.8 informat = 14.8 label = 'HLM4WkTotal', W1B0AGV.HLMMean length = 8 format = 14.8 informat = 14.8 label = 'HLMMean', W1B0AGV.HLMSD length = 8 format = 14.8 informat = 14.8 label = 'HLMSD', W1B0AGV.HLMUCL length = 8 format = 14.8 informat = 14.8 label = 'HLMUCL', W1B0AGV.rate length = 8 format = 11.6 informat = 11.6 label = 'rate', W1B0AGV.labGroupID length = 8 format = 11. informat = 11. label = 'labGroupID', W1BUEVT.syndromeGroupName length = 50 format = $50. informat = $50. label = 'syndromeGroupName', W1BUEVT.syndromeGroupFullName length = 255 format = $255. informat = $255. label = 'syndromeGroupFullName', W1BUEVT.effectiveDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'effectiveDate', W1BUEVT.expiryDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'expiryDate', W1BUEVT.displaySequence length = 8 format = 6. informat = 6. label = 'displaySequence', W1BUEVT.remark length = 500 format = $500. informat = $500. label = 'remark', W1BUEVT.createdBy length = 20 format = $20. informat = $20. label = 'createdBy', W1BUEVT.createdTime length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'createdTime', W1BUEVT.lastUpdatedBy length = 20 format = $20. informat = $20. label = 'lastUpdatedBy', W1BUEVT.lastUpdatedTime length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'lastUpdatedTime' from work.W1B0AGV as W1B0AGV, work.W1BUEVT as W1BUEVT where W1B0AGV.syndromeGroupID = W1BUEVT.syndromeGroupID ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Join **/ /*==========================================================================* * Step: Join A5NBTOIY.BY0002HV * * Transform: Join * * Description: * * * * Source Tables: SnapshotSatScanResult_AED - A5NBTOIY.BD0000EJ * * Sherlock.SnapshotSatScanResult_AED * * Extract - work.W1B0AGV A5NBTOIY.C60001N0 * * Target Table: Join - work.W1GAR6L A5NBTOIY.C60001N3 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HV); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = 0; %macro etls_recordCheck; %let etls_recCheckExist = %eval(%sysfunc(exist(Sherlock.SnapshotSatScanResult_AED, DATA)) or %sysfunc(exist(Sherlock.SnapshotSatScanResult_AED, VIEW))); %if (&etls_recCheckExist) %then %do; proc sql noprint; select count(*) into :etls_recnt from Sherlock.SnapshotSatScanResult_AED; quit; %end; %mend etls_recordCheck; %etls_recordCheck; /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W1GAR6L; quit; proc sql; create view work.W1GAR6L as select SnapshotSatScanResult_AED.syndromeGroupID length = 8 format = 11. informat = 11. label = 'syndromeGroupID', SnapshotSatScanResult_AED.attendanceDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'attendanceDate' from Sherlock.SnapshotSatScanResult_AED as SnapshotSatScanResult_AED, work.W1B0AGV as W1B0AGV where SnapshotSatScanResult_AED.syndromeGroupID = W1B0AGV.syndromeGroupID and SnapshotSatScanResult_AED.attendanceDate = W1B0AGV.attendanceDate and SnapshotSatScanResult_AED.syndromeGroupID NOT IS MISSING ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Join **/ /*==========================================================================* * Step: Join A5NBTOIY.BY0002HW * * Transform: Join * * Description: * * * * Source Tables: Join - work.W1GAR6L A5NBTOIY.C60001N3 * * Join - work.W16944G A5NBTOIY.C60001MZ * * Target Table: Join - work.W1BBHKG A5NBTOIY.C60001N4 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HW); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = -1; /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W1BBHKG; quit; proc sql; create view work.W1BBHKG as select W16944G.AttendanceDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'AttendanceDate', W16944G.syndromeGroupID length = 8 format = 11. informat = 11. label = 'syndromeGroupID', count(W1GAR6L.attendanceDate) as count length = 8 from work.W16944G as W16944G left join work.W1GAR6L as W1GAR6L on ( W16944G.AttendanceDate = W1GAR6L.attendanceDate and W16944G.syndromeGroupID = W1GAR6L.syndromeGroupID ) ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Join **/ /*==========================================================================* * Step: Join A5NBTOIY.BY0002HX * * Transform: Join * * Description: * * * * Source Tables: Join - work.W1G5OJK A5NBTOIY.C60001N2 * * Join - work.W1BBHKG A5NBTOIY.C60001N4 * * Extract - work.W1AQIJO A5NBTOIY.C60001MX * * Target Table: Join - work.W1GMBDR A5NBTOIY.C60001N5 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HX); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = -1; /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W1GMBDR; quit; proc sql; create view work.W1GMBDR as select W1AQIJO.odate length = 8 format = DATE9. informat = DATE9., W1G5OJK.attendanceDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'attendanceDate', W1G5OJK.spatialLevelID length = 50 format = $50. informat = $50. label = 'spatialLevelID', W1G5OJK.groupedCases length = 8 format = 11. informat = 11. label = 'groupedCases', W1G5OJK.dailyCusum length = 8 format = 11.6 informat = 11.6 label = 'dailyCusum', W1G5OJK.dailyEWMACusumSignal length = 8 format = 1. informat = 1. label = 'dailyEWMACusumSignal', W1G5OJK.dailyCumulativePeriod length = 8 format = 6. informat = 6. label = 'dailyCumulativePeriod', W1G5OJK.dailyPrediction length = 8 format = 11.6 informat = 11.6 label = 'dailyPrediction', W1G5OJK.sevenDayMARate length = 8 format = 11.6 informat = 11.6 label = 'sevenDayMARate', W1G5OJK.ccSignal length = 8 format = 1. informat = 1. label = 'ccSignal', W1G5OJK.resultType length = 8 format = 11. informat = 11. label = 'resultType', W1G5OJK.measurementUnit length = 8 format = 4. informat = 4. label = 'measurementUnit', W1G5OJK.year length = 8 format = 6. informat = 6. label = 'year', W1G5OJK.weekNum length = 8 format = 4. informat = 4. label = 'weekNum', W1G5OJK.weekEnding length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'weekEnding', W1G5OJK.month length = 8 format = 4. informat = 4. label = 'month', W1G5OJK.syndromeGroupID length = 8 format = 11. informat = 11. label = 'syndromeGroupID', W1G5OJK.sevenDayMACount length = 8 format = 14.8 informat = 14.8 label = 'sevenDayMACount', W1G5OJK.controlChartUCL1 length = 8 format = 1. informat = 1. label = 'controlChartUCL1', W1G5OJK.controlChartUCL2 length = 8 format = 1. informat = 1. label = 'controlChartUCL2', W1G5OJK.controlChartUCLMR length = 8 format = 1. informat = 1. label = 'controlChartUCLMR', W1G5OJK.EWMAUCL1 length = 8 format = 1. informat = 1. label = 'EWMAUCL1', W1G5OJK.EWMAUCL2 length = 8 format = 1. informat = 1. label = 'EWMAUCL2', W1G5OJK.HLM length = 8 format = 1. informat = 1. label = 'HLM', W1G5OJK.controlChartUCL1Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL1Value', W1G5OJK.controlChartUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL2Value', W1G5OJK.controlChartUCLMRValue length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCLMRValue', W1G5OJK.EWMAPredicationZt length = 8 format = 14.8 informat = 14.8 label = 'EWMAPredicationZt', W1G5OJK.EWMAResidualet length = 8 format = 14.8 informat = 14.8 label = 'EWMAResidualet', W1G5OJK.EWMAUCL1Value length = 8 format = 14.8 informat = 14.8 label = 'EWMAUCL1Value', W1G5OJK.EWMAUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'EWMAUCL2Value', W1G5OJK.HLM4WkTotal length = 8 format = 14.8 informat = 14.8 label = 'HLM4WkTotal', W1G5OJK.HLMMean length = 8 format = 14.8 informat = 14.8 label = 'HLMMean', W1G5OJK.HLMSD length = 8 format = 14.8 informat = 14.8 label = 'HLMSD', W1G5OJK.HLMUCL length = 8 format = 14.8 informat = 14.8 label = 'HLMUCL', W1G5OJK.rate length = 8 format = 11.6 informat = 11.6 label = 'rate', W1G5OJK.labGroupID length = 8 format = 11. informat = 11. label = 'labGroupID', W1G5OJK.syndromeGroupName length = 50 format = $50. informat = $50. label = 'syndromeGroupName', W1G5OJK.syndromeGroupFullName length = 255 format = $255. informat = $255. label = 'syndromeGroupFullName', W1G5OJK.effectiveDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'effectiveDate', W1G5OJK.expiryDate length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'expiryDate', W1G5OJK.displaySequence length = 8 format = 6. informat = 6. label = 'displaySequence', W1G5OJK.remark length = 500 format = $500. informat = $500. label = 'remark', W1G5OJK.createdBy length = 20 format = $20. informat = $20. label = 'createdBy', W1G5OJK.createdTime length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'createdTime', W1G5OJK.lastUpdatedBy length = 20 format = $20. informat = $20. label = 'lastUpdatedBy', W1G5OJK.lastUpdatedTime length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'lastUpdatedTime', W1BBHKG.count length = 8 from work.W1AQIJO as W1AQIJO left join work.W1G5OJK as W1G5OJK on ( W1AQIJO.odate = datepart(W1G5OJK.attendanceDate) ) left join work.W1BBHKG as W1BBHKG on ( W1G5OJK.attendanceDate = W1BBHKG.AttendanceDate ) ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Join **/ /*==========================================================================* * Step: Create Table A5NBTOIY.BY0002HY * * Transform: Create Table * * Description: * * * * Source Table: Join - work.W1GMBDR A5NBTOIY.C60001N5 * * Target Table: Create Table - work.W1GY0QH A5NBTOIY.C60001N6 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HY); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = -1; %let SYSLAST = %nrquote(work.W1GMBDR); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLCreateTable), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W1GY0QH; quit; proc sql ; create view work.W1GY0QH as select W1GMBDR.odate length = 8 format = DATE9. informat = DATE9., case when W1GMBDR.dailyEWMACusumSignal = 1 then W1GMBDR.rate end as dailyEWMACusumSignal length = 8 format = 11.6 informat = 11.6 label = 'dailyEWMACusumSignal', W1GMBDR.sevenDayMARate as sevenDayMARate length = 8 format = 11.6 informat = 11.6 label = 'sevenDayMARate', W1GMBDR.ccSignal length = 8 format = 1. informat = 1. label = 'ccSignal', W1GMBDR.controlChartUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL2Value', W1GMBDR.syndromeGroupFullName length = 255 format = $255. informat = $255. label = 'syndromeGroupFullName', W1GMBDR.count length = 8, W1GMBDR.rate as rate length = 8 format = 11.6 informat = 11.6 label = 'rate' from work.W1GMBDR as W1GMBDR ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Create Table **/ /*==========================================================================* * Step: Delete A5NBTOIY.BY0002HZ * * Transform: Delete * * Description: * * * * Target Table: RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL * * RPTIMAGE.RST_DAT_BGIS_108_001 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002HZ); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); /* Access the data for RptImage */ LIBNAME RPTIMAGE BASE "\\dcdissas54v\sas\CDIS\ReportSnapshot"; %rcSet(&syslibrc); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SqlDelete), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; %macro etls_sql_delete; /* Determine if the table exists */ %let etls_tableExist = %eval(%sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, DATA)) or %sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, VIEW))); /*---- Create a new table ----*/ %if (&etls_tableExist eq 0) %then %do; /* if table does not exist */ %put %str(NOTE: Creating table ...); data RPTIMAGE.RST_DAT_BGIS_108_001; attrib year length = 8; attrib month length = 8; attrib day length = 8; attrib odate length = 8 format = DATE9. informat = DATE9.; attrib Adm_date length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'Adm_date'; attrib syndromeGroupFullName length = $255 format = $255. informat = $255. label = 'syndromeGroupFullName'; attrib sevenDayMARate length = 8 format = 11.6 informat = 11.6 label = 'sevenDayMARate'; attrib rate length = 8 format = 11.6 informat = 11.6 label = 'rate'; attrib controlChartUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL2Value'; attrib dailyEWMACusumSignal length = 8 format = 11.6 informat = 11.6 label = 'dailyEWMACusumSignal'; attrib ccSignal length = 8 format = 1. informat = 1. label = 'ccSignal'; attrib count length = 8; attrib snapshot_id length = $8; attrib day2 length = 8; call missing(of _all_); stop; run; %rcSet(&syserr); %end; /* if table does not exist */ /*---- Delete the target table ----*/ proc sql; delete from RPTIMAGE.RST_DAT_BGIS_108_001 where RST_DAT_BGIS_108_001.snapshot_id = "&snapshot_id" ;quit; %rcSet(&syscc); %mend etls_sql_delete; /* execute etls_sql_delete */ %etls_sql_delete %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Delete **/ /*==========================================================================* * Step: Insert Rows A5NBTOIY.BY0002I0 * * Transform: Insert Rows * * Description: * * * * Source Table: Create Table - work.W1GY0QH A5NBTOIY.C60001N6 * * Target Table: RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL * * RPTIMAGE.RST_DAT_BGIS_108_001 * *==========================================================================*/ %let transformID = %quote(A5NBTOIY.BY0002I0); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let etls_recnt = -1; %let SYSLAST = %nrquote(work.W1GY0QH); /* Runtime statistics macros */ %etls_setPerfInit; %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLInsertRows), metrNam6=_DISROWCNT, metrDef6=Count32) ; %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; %macro etls_sql_insert_rows; /* Determine if the table exists */ %let etls_tableExist = %eval(%sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, DATA)) or %sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, VIEW))); /*---- Create a new table ----*/ %if (&etls_tableExist eq 0) %then %do; /* if table does not exist */ %put %str(NOTE: Creating table ...); data RPTIMAGE.RST_DAT_BGIS_108_001; attrib year length = 8; attrib month length = 8; attrib day length = 8; attrib odate length = 8 format = DATE9. informat = DATE9.; attrib Adm_date length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'Adm_date'; attrib syndromeGroupFullName length = $255 format = $255. informat = $255. label = 'syndromeGroupFullName'; attrib sevenDayMARate length = 8 format = 11.6 informat = 11.6 label = 'sevenDayMARate'; attrib rate length = 8 format = 11.6 informat = 11.6 label = 'rate'; attrib controlChartUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL2Value'; attrib dailyEWMACusumSignal length = 8 format = 11.6 informat = 11.6 label = 'dailyEWMACusumSignal'; attrib ccSignal length = 8 format = 1. informat = 1. label = 'ccSignal'; attrib count length = 8; attrib snapshot_id length = $8; attrib day2 length = 8; call missing(of _all_); stop; run; %rcSet(&syserr); %end; /* if table does not exist */ /*---- Insert rows into target table ----*/ proc sql; insert into RPTIMAGE.RST_DAT_BGIS_108_001 (year, month, day, odate, Adm_date, syndromeGroupFullName, sevenDayMARate, rate, controlChartUCL2Value, dailyEWMACusumSignal, ccSignal, count, snapshot_id, day2) select distinct year(W1GY0QH.odate) as year length = 8, month(W1GY0QH.odate) as month length = 8, day(W1GY0QH.odate) as day length = 8, W1GY0QH.odate length = 8 format = DATE9. informat = DATE9., . as Adm_date length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'Adm_date', W1GY0QH.syndromeGroupFullName length = 255 format = $255. informat = $255. label = 'syndromeGroupFullName', W1GY0QH.sevenDayMARate length = 8 format = 11.6 informat = 11.6 label = 'sevenDayMARate', W1GY0QH.rate length = 8 format = 11.6 informat = 11.6 label = 'rate', W1GY0QH.controlChartUCL2Value length = 8 format = 14.8 informat = 14.8 label = 'controlChartUCL2Value', W1GY0QH.dailyEWMACusumSignal length = 8 format = 11.6 informat = 11.6 label = 'dailyEWMACusumSignal', W1GY0QH.ccSignal length = 8 format = 1. informat = 1. label = 'ccSignal', W1GY0QH.count length = 8, "&snapshot_id" as snapshot_id length = 8, . as day2 length = 8 from work.W1GY0QH as W1GY0QH ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); %mend etls_sql_insert_rows; /* execute etls_sql_insert_rows */ %etls_sql_insert_rows; /*---- Start of Post-Process Code ----*/ data RPTIMAGE.RST_DAT_BGIS_108_001; set RPTIMAGE.RST_DAT_BGIS_108_001; if count=0 then count=.; else count=count; run; /*---- End of Post-Process Code ----*/ %rcSet(&syserr); %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end Insert Rows **/ /*---- Start of Post-Process Code ----*/ %let log_endTime = %sysfunc(datetime(),datetime21.2); proc sql; insert into BGIS.DIS_LOG (DIS_JOB_ID, LAST_SNAPSHOT_ID, RUN_DATE, REMARK) values ("&etls_jobName", "&snapshot_id", "&log_endTime"dt, ""); run; /*---- End of Post-Process Code ----*/ %rcSet(&syserr); %rcSet(&sqlrc); %let etls_endTime = %sysfunc(datetime(),datetime.); /* Turn off performance statistics collection */ data _null_; if "&_perfinit" eq "1" then call execute('%perfend;'); run;
... View more