Passing a macro variable in to a subjob

[Feelgod]

   I can pass a macro variable in to a subjob if there is no control flow added. However, when I embrace the subjob with conditional start and conditional end, the macro variable defined in the precode of the job cannot be passed into subjob inside the job.

[Haikuo]

Make it unique (no other duplicated macro variable names inside or outside the macro), make it global.

[Feelgod]

The macro is not duplicated, but how to make it global?

(by the way I will update this post with 2 pictures, please take a look and give some advice, thanks)

[gergely_batho]

Hi,

I made a small test:

I used a User Written transformation in the Super Job with this code: %let testMac=XXXXXXXXX;

I used a User Written transformation in the Sub Job with this code: %put &testMac.;

I have put Sub Job into Super Job surrounded with Conditional Start  and Conditional End.

It is working.

What is your setup? What version of DI Studio are you using?

[Feelgod]

I have tried this but it doesn't work. My DI version is 4.9

(by the way I will update this post with 2 pictures, please take a look and give some advice, thanks)

[Feelgod]

For more information, 2 pictures are attached here for reference.

[gergely_batho]

Your outer job is called A_testing.

Your inner jog is called DIS-JOB-BGIS-108-001.

You create a macro variable in the outer job's pre-code. (%let snapshot_id=20171231)

What kind of code is in your inner job? Is there for example something like this:

%put &snapshot_id.;

-and you get a warning about macro reference not found?

Are you also using DI parameters in some of the jobs or transformations? A DI parameter is translated to a macro variable. As @Hai.kuo suggested this can hide and outer macro variable definition.

When you use Conditional Start/End the code inside the inner job is put inside a macro. If there is a definition for the same macro variable (snapshot_id) this can hide the outer variable.

To make the macro variable global use this code:

%global snapshot_id;

-this code should go in your inner job pre-code section. But I'm not sure it will help, I would need more info about your job.

If it is possible, attach  the code that is generated by the outer job.

Thanks

[Feelgod]

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;

[Patrick]

Next time please add your code as a text attachment - this way it's much easier for us to scroll through this thread - and if it's DI then it should be in the "data management" section.

Looking into the DI generated code the very first time you reference "snapshot_id" you set it %global and there is no %local statement anywhere in the code. There is also no "rsubmit" or anything like this so all your code runs in a single session.

This means "&snapshot_id" should always be available on global level no matter where you set the value.

You don't really tell us what the problem is but just scanning through your code I can see that you populate "snapshot_id" twice but with different values.
%let snapshot_id = 20171231;

%let snapshot_id = 20161231;

So I assume you don't really get an error but an unexpected result - which could well be caused by populating "snapshot_id" twice but with different values.

[gergely_batho]

What exactly is in the log?

Error? Warning? Nothing?

Instead of simply: %put &snapshot_id.;

Use this code:

%put *****************************;

%put &snapshot_id.;

%put *****************************;

Please also change this line:

*%LET snapshot_id=20131231;

to:

/*%LET snapshot_id=20131231;*/

(this is the right way using comments inside a macro)

This way it will be easier to identify the log segment  that you need.

Did you know, that your Conditional Start also includes a Pre-Code section? With this code:

%let snapshot_id = 20161231;

Message was edited by: Gergely Bathó