Passing a macro variable in to a subjob

Reply
Occasional Contributor
Posts: 5

Passing a macro variable in to a subjob

   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.

Respected Advisor
Posts: 3,156

Re: Passing a macro variable in to a subjob

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

Occasional Contributor
Posts: 5

Re: Passing a macro variable in to a subjob

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)

SAS Employee
Posts: 340

Re: Passing a macro variable in to a subjob

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?

Occasional Contributor
Posts: 5

Re: Passing a macro variable in to a subjob

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)

Occasional Contributor
Posts: 5

Re: Passing a macro variable in to a subjob

For more information, 2 pictures are attached here for reference.Snapshot P1.JPGSnapshot P2.JPG

SAS Employee
Posts: 340

Re: Passing a macro variable in to a subjob

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

Occasional Contributor
Posts: 5

Re: Passing a macro variable in to a subjob

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.

Snapshot P3.JPGSnapshot P4.JPG

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(Smiley Wink;

%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(Smiley Wink;

%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(Smiley Wink;

%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;

Respected Advisor
Posts: 4,137

Re: Passing a macro variable in to a subjob

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.

SAS Employee
Posts: 340

Re: Passing a macro variable in to a subjob

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ó

Ask a Question
Discussion stats
  • 9 replies
  • 1005 views
  • 6 likes
  • 4 in conversation