topic Re: Passing a macro variable in to a subjob in SAS Programming

Passing a macro variable in to a subjob

Feelgod — Thu, 19 Mar 2015 03:32:13 GMT

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.

Re: Passing a macro variable in to a subjob

Haikuo — Thu, 19 Mar 2015 13:25:57 GMT

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

Re: Passing a macro variable in to a subjob

gergely_batho — Thu, 19 Mar 2015 13:28:49 GMT

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?

Re: Passing a macro variable in to a subjob

Feelgod — Mon, 23 Mar 2015 03:35:22 GMT

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)

Re: Passing a macro variable in to a subjob

Feelgod — Mon, 23 Mar 2015 03:38:47 GMT

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)

Re: Passing a macro variable in to a subjob

Feelgod — Mon, 23 Mar 2015 03:42:01 GMT

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

Re: Passing a macro variable in to a subjob

gergely_batho — Mon, 23 Mar 2015 07:52:37 GMT

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

Re: Passing a macro variable in to a subjob

Feelgod — Mon, 23 Mar 2015 08:54:40 GMT

Yes, I have added %put &snapshot_id.; in the precode of DIS-JOB-BGIS-108-001 but still not work.

And later I added %global snapshot_id; in the precode of A_testing and still not working.

I have checked that I haven't used any parameter for any transformation.

Attached are the pictures of the precode of the 2 jobs after accepting advise from @Hai.kuo and you.

Here are the codes for the jobs:

1) A_testing Code:

/****************************************************************************

* Job: A_testing A5NBTOIY.BV0004VJ *

* Description: *

* *

* Metadata Server: dcdissas53v.cdisdev.chp.hksarg *

* Port: 8561 *

* Location: /DIS/Jobs *

* *

* Server: SASApp A5NBTOIY.AS000003 *

* *

* Source Table: REPORT_TO_DI_MAPPING - A5NBTOIY.BD0000QH *

* SOL.REPORT_TO_DI_MAPPING *

* Target Table: RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL *

* RPTIMAGE.RST_DAT_BGIS_108_001 *

* *

* Generated on: 2015年03月23日星期一下午04時42分57秒 CST *

* Generated by: super2@CDISDEV *

* Version: SAS Data Integration Studio 4.9 *

****************************************************************************/

/* Generate the process id for job */

%put Process ID: &SYSJOBID;

/* General macro variables */

%let jobID = %quote(A5NBTOIY.BV0004VJ);

%let etls_jobName = %nrquote(A_testing);

%let etls_userID = %nrquote(super2@CDISDEV);

%global applName;

data _null_;

applName="SAS Data Integration Studio";

call symput('applName',%nrstr(applName));

run;

/* Performance Statistics require ARM_PROC sub-system */

%macro etls_startPerformanceStats;

%log4sas();

%log4sas_logger(Perf.ARM, 'level=info');

options armagent=log4sas armsubsys=(ARM_PROC);

%global _armexec;

%let _armexec = 1;

%perfinit(applname="&applName");

%global etls_recnt;

%let etls_recnt=-1;

%mend;

%etls_startPerformanceStats;

%macro etls_setArmagent;

%let armagentLength = %length(%sysfunc(getoption(armagent)));

%if (&armagentLength eq 0) %then

%do;

%log4sas();

%log4sas_logger(Perf.ARM, 'level=info');

options armagent=log4sas armsubsys=(ARM_PROC);

%end;

%mend etls_setArmagent;

%macro etls_setPerfInit;

%if "&_perfinit" eq "0" %then

%do;

%etls_setArmagent;

%global _armexec;

%let _armexec = 1;

%perfinit(applname="&applName");

%end;

%mend etls_setPerfInit;

/* Setup to capture return codes */

%global job_rc trans_rc sqlrc syscc;

%let sysrc = 0;

%let job_rc = 0;

%let trans_rc = 0;

%let sqlrc = 0;

%let syscc = 0;

%global etls_stepStartTime;

/* initialize syserr to 0 */

data _null_; run;

%macro rcSet(error);

%if (&error gt &trans_rc) %then

%let trans_rc = &error;

%if (&error gt &job_rc) %then

%let job_rc = &error;

%mend rcSet;

%macro rcSetDS(error);

if &error gt input(symget('trans_rc'),12.) then

call symput('trans_rc',trim(left(put(&error,12.))));

if &error gt input(symget('job_rc'),12.) then

call symput('job_rc',trim(left(put(&error,12.))));

%mend rcSetDS;

/* Create metadata macro variables */

%let IOMServer = %nrquote(SASApp);

%let metaPort = %nrquote(8561);

%let metaServer = %nrquote(dcdissas53v.cdisdev.chp.hksarg);

/* Set metadata options */

options metaport = &metaPort

metaserver = "&metaServer";

/* Setup for capturing job status */

%let etls_startTime = %sysfunc(datetime(),datetime.);

%let etls_recordsBefore = 0;

%let etls_recordsAfter = 0;

%let etls_lib = 0;

%let etls_table = 0;

%global etls_debug;

%macro etls_setDebug;

%if %str(&etls_debug) ne 0 %then

OPTIONS MPRINT%str(;);

%mend;

%etls_setDebug;

/*---- Start of Pre-Process Code ----*/

%global snapshot_id;

%let snapshot_id = 20171231;

/*---- End of Pre-Process Code ----*/

%rcSet(&syserr);

%rcSet(&sqlrc);

/*==========================================================================*

* Step: Retrieve Related Rows A5NBTOIY.BY000BI9 *

* Transform: Extract *

* Description: *

* *

* Source Table: REPORT_TO_DI_MAPPING - A5NBTOIY.BD0000QH *

* SOL.REPORT_TO_DI_MAPPING *

* Target Table: Extract - work.WEGSOOZ A5NBTOIY.C600087E *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY000BI9);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

/* Access the data for SOLITARY */

LIBNAME SOL BASE "\\dcdissas54v\sas\CDIS\Solitary File";

%rcSet(&syslibrc);

%let etls_recCheckExist = 0;

%let etls_recnt = 0;

%macro etls_recordCheck;

%let etls_recCheckExist = %eval(%sysfunc(exist(SOL.REPORT_TO_DI_MAPPING, DATA)) or

%sysfunc(exist(SOL.REPORT_TO_DI_MAPPING, VIEW)));

%if (&etls_recCheckExist) %then

%do;

%local etls_syntaxcheck;

%let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck));

/* Turn off syntaxcheck option to perform following steps */

options nosyntaxcheck;

proc contents data = SOL.REPORT_TO_DI_MAPPING out = work.etls_contents(keep = nobs) noprint;

run;

data _null_;

set work.etls_contents (obs = 1);

call symput("etls_recnt", left(put(nobs,32.)));

run;

proc datasets lib = work nolist nowarn memtype = (data view);

delete etls_contents;

quit;

/* Reset syntaxcheck option to previous setting */

options &etls_syntaxcheck;

%end;

%mend etls_recordCheck;

%etls_recordCheck;

%let SYSLAST = %nrquote(SOL.REPORT_TO_DI_MAPPING);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

/*---- Map the columns ----*/

proc datasets lib = work nolist nowarn memtype = (data view);

delete WEGSOOZ;

quit;

%put %str(NOTE: Mapping columns ...);

proc sql;

create view work.WEGSOOZ as

select

FLAG,

REPORT,

JOB_SEQUENCE

label = 'JOB_SEQUENCE',

DI_JOB

from &SYSLAST

where REPORT='RPT-BGIS-022W'

;

quit;

%let SYSLAST = work.WEGSOOZ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Retrieve Related Rows **/

/*==========================================================================*

* Step: Create Flag Macro A5NBTOIY.BY000BJ5 *

* Transform: User Written *

* Description: *

* *

* Source Table: Extract - work.WEGSOOZ A5NBTOIY.C600087E *

* Target Table: User Written - work.WI81FRR A5NBTOIY.C600087U *

* *

* User Written: SourceCode *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY000BJ5);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

%let etls_recnt = -1;

%let SYSLAST = %nrquote(work.WEGSOOZ);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|UserWritten), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%let _INPUT_count = 1;

%let _INPUT = work.WEGSOOZ;

%let _INPUT_connect = ;

%let _INPUT_engine = ;

%let _INPUT_memtype = VIEW;

%let _INPUT_options = %nrquote();

%let _INPUT_alter = %nrquote();

%let _INPUT_path = %nrquote(/Extract_A5NBTOIY.C600087E%(WorkTable%));

%let _INPUT_type = 1;

%let _INPUT_label = %nrquote();

%let _INPUT1 = work.WEGSOOZ;

%let _INPUT1_connect = ;

%let _INPUT1_engine = ;

%let _INPUT1_memtype = VIEW;

%let _INPUT1_options = %nrquote();

%let _INPUT1_alter = %nrquote();

%let _INPUT1_path = %nrquote(/Extract_A5NBTOIY.C600087E%(WorkTable%));

%let _INPUT1_type = 1;

%let _INPUT1_label = %nrquote();

%let _OUTPUT_count = 1;

%let _OUTPUT = work.WI81FRR;

%let _OUTPUT_connect = ;

%let _OUTPUT_engine = ;

%let _OUTPUT_memtype = DATA;

%let _OUTPUT_options = %nrquote();

%let _OUTPUT_alter = %nrquote();

%let _OUTPUT_path = %nrquote(/User Written_A5NBTOIY.C600087U%(WorkTable%));

%let _OUTPUT_type = 1;

%let _OUTPUT_label = %nrquote();

%let _OUTPUT_col_count = 0;

%let _OUTPUT1 = work.WI81FRR;

%let _OUTPUT1_connect = ;

%let _OUTPUT1_engine = ;

%let _OUTPUT1_memtype = DATA;

%let _OUTPUT1_options = %nrquote();

%let _OUTPUT1_alter = %nrquote();

%let _OUTPUT1_path = %nrquote(/User Written_A5NBTOIY.C600087U%(WorkTable%));

%let _OUTPUT1_type = 1;

%let _OUTPUT1_label = %nrquote();

%let _OUTPUT1_col_count = 0;

/*---- Start of User Written Code ----*/

data _null_;

set work.WEGSOOZ ;

call symput('flag',flag);

run;

/*---- End of User Written Code ----*/

%rcSet(&syserr);

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Create Flag Macro **/

/*==========================================================================*

* Step: Conditional Start A5NBTOIY.BY000BIJ *

* Transform: Conditional Start *

* Description: *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY000BIJ);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

%let ETLS_SYSLAST = &SYSLAST;

/*---- Start of Pre-Process Code ----*/

%let snapshot_id = 20161231;

/*---- End of Pre-Process Code ----*/

%rcSet(&syserr);

%rcSet(&sqlrc);

%let SYSLAST = &ETLS_SYSLAST;

%macro etls_conditionWH2ICMT;

%local etls_conditionTrue;

%let etls_conditionTrue = %eval(&flag=N);

%if (&etls_conditionTrue=0) %then

%do;

%put ETLS_DIAG: Condition flow did NOT execute, condition was &flag=N;

%goto exitetls_conditionWH2ICMT;

%end;

%else

%do;

%put ETLS_DIAG: Condition flow did execute, condition was &flag=N;

%end;

/*==========================================================================*

* Step: Set Flag to Y A5NBTOIY.BY000BJ6 *

* Transform: User Written *

* Description: *

* *

* Target Table: User Written - work.WI844FW A5NBTOIY.C600087V *

* *

* User Written: SourceCode *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY000BJ6);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|UserWritten), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%let _INPUT_count = 0;

%let _OUTPUT_count = 1;

%let _OUTPUT = work.WI844FW;

%let _OUTPUT_connect = ;

%let _OUTPUT_engine = ;

%let _OUTPUT_memtype = DATA;

%let _OUTPUT_options = %nrquote();

%let _OUTPUT_alter = %nrquote();

%let _OUTPUT_path = %nrquote(/User Written_A5NBTOIY.C600087V%(WorkTable%));

%let _OUTPUT_type = 1;

%let _OUTPUT_label = %nrquote();

%let _OUTPUT_col_count = 0;

%let _OUTPUT1 = work.WI844FW;

%let _OUTPUT1_connect = ;

%let _OUTPUT1_engine = ;

%let _OUTPUT1_memtype = DATA;

%let _OUTPUT1_options = %nrquote();

%let _OUTPUT1_alter = %nrquote();

%let _OUTPUT1_path = %nrquote(/User Written_A5NBTOIY.C600087V%(WorkTable%));

%let _OUTPUT1_type = 1;

%let _OUTPUT1_label = %nrquote();

%let _OUTPUT1_col_count = 0;

/*---- Start of User Written Code ----*/

data SOL.REPORT_TO_DI_MAPPING;

set SOL.REPORT_TO_DI_MAPPING;

if REPORT='RPT-BGIS-022W' then FLAG='Y';

run;

/*---- End of User Written Code ----*/

%rcSet(&syserr);

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Set Flag to Y **/

/****************************************************************************

* Job: DIS-JOB-BGIS-108-001 A5NBTOIY.BV00004F *

* Description: *

* *

* Metadata Server: dcdissas53v.cdisdev.chp.hksarg *

* Port: 8561 *

* Location: /DIS/Jobs *

* *

* Server: SASApp A5NBTOIY.AS000003 *

* *

* Source Tables: DIS_LOG - BGIS.DIS_LOG A5NBTOIY.BD00010K *

* WEEK_NUMBER - SOL.WEEK_NUMBER A5NBTOIY.BD00000A *

* CalculatedResult_AED - A5NBTOIY.BD0000BO *

* Sherlock.CalculatedResult_AED *

* SyndromeGroup_AED - A5NBTOIY.BD0000F0 *

* Sherlock.SyndromeGroup_AED *

* SnapshotSatScanResult_AED - A5NBTOIY.BD0000EJ *

* Sherlock.SnapshotSatScanResult_AED *

* SatScanStatus_AED - A5NBTOIY.BD000434 *

* ShLog.SatScanStatus_AED *

* Target Tables: RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL *

* RPTIMAGE.RST_DAT_BGIS_108_001 *

* RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL *

* RPTIMAGE.RST_DAT_BGIS_108_001 *

* *

* Generated on: 2015年03月23日星期一下午04時42分57秒 CST *

* Generated by: super2@CDISDEV *

* Version: SAS Data Integration Studio 4.9 *

****************************************************************************/

/* Generate the process id for job */

%put Process ID: &SYSJOBID;

/* General macro variables */

%let jobID = %quote(A5NBTOIY.BV00004F);

%let etls_jobName = %nrquote(DIS-JOB-BGIS-108-001);

%let etls_userID = %nrquote(super2@CDISDEV);

/* Create metadata macro variables */

%let IOMServer = %nrquote(SASApp);

%let metaPort = %nrquote(8561);

%let metaServer = %nrquote(dcdissas53v.cdisdev.chp.hksarg);

/* Set metadata options */

options metaport = &metaPort

metaserver = "&metaServer";

/* Setup for capturing job status */

%let etls_startTime = %sysfunc(datetime(),datetime.);

%let etls_recordsBefore = 0;

%let etls_recordsAfter = 0;

%let etls_lib = 0;

%let etls_table = 0;

%global etls_debug;

%macro etls_setDebug;

%if %str(&etls_debug) ne 0 %then

OPTIONS MPRINT%str(;);

%mend;

%etls_setDebug;

/*---- Start of Pre-Process Code ----*/

*%LET snapshot_id=20131231;

%put &snapshot_id.;

/*---- End of Pre-Process Code ----*/

%rcSet(&syserr);

%rcSet(&sqlrc);

/*==========================================================================*

* Step: Extract A5NBTOIY.BY0002HO *

* Transform: Extract *

* Description: *

* *

* Source Table: DIS_LOG - BGIS.DIS_LOG A5NBTOIY.BD00010K *

* Target Table: Extract - work.W1ALZ89 A5NBTOIY.C60001MW *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002HO);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

/* Access the data for CDIS_BGIS */

LIBNAME BGIS ORACLE DBSERVER_MAX_BYTES=1 DBCLIENT_MAX_BYTES=1 PATH=ANASIT0 SCHEMA=CDIS_BGIS AUTHDOMAIN="bgisdbAuth" ;

%rcSet(&syslibrc);

%let etls_recnt = 0;

%macro etls_recordCheck;

%let etls_recCheckExist = %eval(%sysfunc(exist(BGIS.DIS_LOG, DATA)) or

%sysfunc(exist(BGIS.DIS_LOG, VIEW)));

%if (&etls_recCheckExist) %then

%do;

proc sql noprint;

select count(*) into :etls_recnt from BGIS.DIS_LOG;

quit;

%end;

%mend etls_recordCheck;

%etls_recordCheck;

%let SYSLAST = %nrquote(BGIS.DIS_LOG);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

/*---- Map the columns ----*/

proc datasets lib = work nolist nowarn memtype = (data view);

delete W1ALZ89;

quit;

%put %str(NOTE: Mapping columns ...);

proc sql;

create view work.W1ALZ89 as

select

DIS_JOB_ID,

LAST_SNAPSHOT_ID,

RUN_DATE,

REMARK

from &SYSLAST

;

quit;

%let SYSLAST = work.W1ALZ89;

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Extract **/

/*==========================================================================*

* Step: Extract A5NBTOIY.BY0002HP *

* Transform: Extract *

* Description: *

* *

* Source Table: WEEK_NUMBER - SOL.WEEK_NUMBER A5NBTOIY.BD00000A *

* Target Table: Extract - work.W1AQIJO A5NBTOIY.C60001MX *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002HP);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

%let etls_recCheckExist = 0;

%let etls_recnt = 0;

%macro etls_recordCheck;

%let etls_recCheckExist = %eval(%sysfunc(exist(SOL.WEEK_NUMBER, DATA)) or

%sysfunc(exist(SOL.WEEK_NUMBER, VIEW)));

%if (&etls_recCheckExist) %then

%do;

%local etls_syntaxcheck;

%let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck));

/* Turn off syntaxcheck option to perform following steps */

options nosyntaxcheck;

proc contents data = SOL.WEEK_NUMBER out = work.etls_contents(keep = nobs) noprint;

run;

data _null_;

set work.etls_contents (obs = 1);

call symput("etls_recnt", left(put(nobs,32.)));

run;

proc datasets lib = work nolist nowarn memtype = (data view);

delete etls_contents;

quit;

/* Reset syntaxcheck option to previous setting */

options &etls_syntaxcheck;

%end;

%mend etls_recordCheck;

%etls_recordCheck;

%let SYSLAST = %nrquote(SOL.WEEK_NUMBER);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

/*---- Map the columns ----*/

proc datasets lib = work nolist nowarn memtype = (data view);

delete W1AQIJO;

quit;

%put %str(NOTE: Mapping columns ...);

proc sql;

create view work.W1AQIJO as

select

odate

from &SYSLAST

where year(ODATE)>=input(substr("&SNAPSHOT_ID",1,4),BEST.)-2 and ODATE<=input("&SNAPSHOT_ID",yymmdd8.)-1

;

quit;

%let SYSLAST = work.W1AQIJO;

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Extract **/

/*==========================================================================*

* Step: Join A5NBTOIY.BY0002HR *

* Transform: Join *

* Description: *

* *

* Source Table: SatScanStatus_AED - A5NBTOIY.BD000434 *

* ShLog.SatScanStatus_AED *

* Target Table: Join - work.W16944G A5NBTOIY.C60001MZ *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002HR);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

/* Access the data for Sherlock_Log */

LIBNAME ShLog ODBC DATASRC=sherlock_log_chp SCHEMA=dbo AUTHDOMAIN="SherlLogCHPAuth" ;

%rcSet(&syslibrc);

%let etls_recnt = 0;

%macro etls_recordCheck;

%let etls_recCheckExist = %eval(%sysfunc(exist(ShLog.SatScanStatus_AED, DATA)) or

%sysfunc(exist(ShLog.SatScanStatus_AED, VIEW)));

%if (&etls_recCheckExist) %then

%do;

proc sql noprint;

select count(*) into :etls_recnt from ShLog.SatScanStatus_AED;

quit;

%end;

%mend etls_recordCheck;

%etls_recordCheck;

%let SYSLAST = %nrquote(ShLog.SatScanStatus_AED);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

proc datasets lib = work nolist nowarn memtype = (data view);

delete W16944G;

quit;

proc sql;

create view work.W16944G as

select

SatScanStatus_AED.attendanceDate length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'AttendanceDate',

SatScanStatus_AED.syndromeGroupID length = 8

format = 11.

informat = 11.

label = 'syndromeGroupID'

from

ShLog.SatScanStatus_AED as SatScanStatus_AED

where

SatScanStatus_AED.complete = 1

;

quit;

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Join **/

/*==========================================================================*

* Step: Extract A5NBTOIY.BY0002HS *

* Transform: Extract *

* Description: *

* *

* Source Table: CalculatedResult_AED - A5NBTOIY.BD0000BO *

* Sherlock.CalculatedResult_AED *

* Target Table: Extract - work.W1B0AGV A5NBTOIY.C60001N0 *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002HS);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

/* Access the data for Sherlock */

LIBNAME Sherlock ODBC DATASRC=sherlock_chp SCHEMA=dbo AUTHDOMAIN="sherlCHPAuth" ;

%rcSet(&syslibrc);

%let etls_recnt = 0;

%macro etls_recordCheck;

%let etls_recCheckExist = %eval(%sysfunc(exist(Sherlock.CalculatedResult_AED, DATA)) or

%sysfunc(exist(Sherlock.CalculatedResult_AED, VIEW)));

%if (&etls_recCheckExist) %then

%do;

proc sql noprint;

select count(*) into :etls_recnt from Sherlock.CalculatedResult_AED;

quit;

%end;

%mend etls_recordCheck;

%etls_recordCheck;

%let SYSLAST = %nrquote(Sherlock.CalculatedResult_AED);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

/*---- Map the columns ----*/

proc datasets lib = work nolist nowarn memtype = (data view);

delete W1B0AGV;

quit;

data _null_;

put "NOTE: The following column(s) do not have a column mapping, so the"

" value(s) will be set to missing: labGroupID";

run;

%put %str(NOTE: Mapping columns ...);

proc sql;

create view work.W1B0AGV as

select

attendanceDate,

spatialLevelID,

groupedCases,

dailyCusum

format = 11.6

informat = 11.6,

dailyEWMACusumSignal,

dailyCumulativePeriod,

dailyPrediction

format = 11.6

informat = 11.6,

sevenDayMARate

format = 11.6

informat = 11.6,

ccSignal,

resultType,

measurementUnit,

year,

weekNum,

weekEnding,

month,

syndromeGroupID,

sevenDayMACount

format = 14.8

informat = 14.8,

controlChartUCL1,

controlChartUCL2,

controlChartUCLMR,

EWMAUCL1,

EWMAUCL2,

HLM,

controlChartUCL1Value

format = 14.8

informat = 14.8,

controlChartUCL2Value

format = 14.8

informat = 14.8,

controlChartUCLMRValue

format = 14.8

informat = 14.8,

EWMAPredicationZt

format = 14.8

informat = 14.8,

EWMAResidualet

format = 14.8

informat = 14.8,

EWMAUCL1Value

format = 14.8

informat = 14.8,

EWMAUCL2Value

format = 14.8

informat = 14.8,

HLM4WkTotal

format = 14.8

informat = 14.8,

HLMMean

format = 14.8

informat = 14.8,

HLMSD

format = 14.8

informat = 14.8,

HLMUCL

format = 14.8

informat = 14.8,

rate

format = 11.6

informat = 11.6,

. as labGroupID length = 8

format = 11.

informat = 11.

label = 'labGroupID'

from &SYSLAST

where year(attendanceDate)>=input(substr("&SNAPSHOT_ID",1,4),BEST.)-2 and year(attendanceDate)<=input(substr("&SNAPSHOT_ID",1,4),BEST.) and spatialLevelID="2409" and resultType=3695 and measurementUnit=2

;

quit;

%let SYSLAST = work.W1B0AGV;

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Extract **/

/*==========================================================================*

* Step: Extract A5NBTOIY.BY0002HT *

* Transform: Extract *

* Description: *

* *

* Source Table: SyndromeGroup_AED - A5NBTOIY.BD0000F0 *

* Sherlock.SyndromeGroup_AED *

* Target Table: Extract - work.W1BUEVT A5NBTOIY.C60001N1 *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002HT);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

%let etls_recnt = 0;

%macro etls_recordCheck;

%let etls_recCheckExist = %eval(%sysfunc(exist(Sherlock.SyndromeGroup_AED, DATA)) or

%sysfunc(exist(Sherlock.SyndromeGroup_AED, VIEW)));

%if (&etls_recCheckExist) %then

%do;

proc sql noprint;

select count(*) into :etls_recnt from Sherlock.SyndromeGroup_AED;

quit;

%end;

%mend etls_recordCheck;

%etls_recordCheck;

%let SYSLAST = %nrquote(Sherlock.SyndromeGroup_AED);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

/*---- Map the columns ----*/

proc datasets lib = work nolist nowarn memtype = (data view);

delete W1BUEVT;

quit;

%put %str(NOTE: Mapping columns ...);

proc sql;

create view work.W1BUEVT as

select

syndromeGroupID,

syndromeGroupName,

syndromeGroupFullName,

effectiveDate,

expiryDate,

displaySequence,

remark,

createdBy,

createdTime,

lastUpdatedBy,

lastUpdatedTime

from &SYSLAST

where syndromeGroupID=2 and datepart(effectiveDate)<=input("&SNAPSHOT_ID",YYMMDD8.) and (datepart(expiryDate)>=input("&SNAPSHOT_ID",YYMMDD8.) or missing(expiryDate))

;

quit;

%let SYSLAST = work.W1BUEVT;

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Extract **/

/*==========================================================================*

* Step: Join A5NBTOIY.BY0002HU *

* Transform: Join *

* Description: *

* *

* Source Tables: Extract - work.W1BUEVT A5NBTOIY.C60001N1 *

* Extract - work.W1B0AGV A5NBTOIY.C60001N0 *

* Target Table: Join - work.W1G5OJK A5NBTOIY.C60001N2 *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002HU);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

%let etls_recnt = -1;

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

proc datasets lib = work nolist nowarn memtype = (data view);

delete W1G5OJK;

quit;

proc sql;

create view work.W1G5OJK as

select

W1B0AGV.attendanceDate length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'attendanceDate',

W1B0AGV.spatialLevelID length = 50

format = $50.

informat = $50.

label = 'spatialLevelID',

W1B0AGV.groupedCases length = 8

format = 11.

informat = 11.

label = 'groupedCases',

W1B0AGV.dailyCusum length = 8

format = 11.6

informat = 11.6

label = 'dailyCusum',

W1B0AGV.dailyEWMACusumSignal length = 8

format = 1.

informat = 1.

label = 'dailyEWMACusumSignal',

W1B0AGV.dailyCumulativePeriod length = 8

format = 6.

informat = 6.

label = 'dailyCumulativePeriod',

W1B0AGV.dailyPrediction length = 8

format = 11.6

informat = 11.6

label = 'dailyPrediction',

W1B0AGV.sevenDayMARate length = 8

format = 11.6

informat = 11.6

label = 'sevenDayMARate',

W1B0AGV.ccSignal length = 8

format = 1.

informat = 1.

label = 'ccSignal',

W1B0AGV.resultType length = 8

format = 11.

informat = 11.

label = 'resultType',

W1B0AGV.measurementUnit length = 8

format = 4.

informat = 4.

label = 'measurementUnit',

W1B0AGV.year length = 8

format = 6.

informat = 6.

label = 'year',

W1B0AGV.weekNum length = 8

format = 4.

informat = 4.

label = 'weekNum',

W1B0AGV.weekEnding length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'weekEnding',

W1B0AGV.month length = 8

format = 4.

informat = 4.

label = 'month',

W1B0AGV.syndromeGroupID length = 8

format = 11.

informat = 11.

label = 'syndromeGroupID',

W1B0AGV.sevenDayMACount length = 8

format = 14.8

informat = 14.8

label = 'sevenDayMACount',

W1B0AGV.controlChartUCL1 length = 8

format = 1.

informat = 1.

label = 'controlChartUCL1',

W1B0AGV.controlChartUCL2 length = 8

format = 1.

informat = 1.

label = 'controlChartUCL2',

W1B0AGV.controlChartUCLMR length = 8

format = 1.

informat = 1.

label = 'controlChartUCLMR',

W1B0AGV.EWMAUCL1 length = 8

format = 1.

informat = 1.

label = 'EWMAUCL1',

W1B0AGV.EWMAUCL2 length = 8

format = 1.

informat = 1.

label = 'EWMAUCL2',

W1B0AGV.HLM length = 8

format = 1.

informat = 1.

label = 'HLM',

W1B0AGV.controlChartUCL1Value length = 8

format = 14.8

informat = 14.8

label = 'controlChartUCL1Value',

W1B0AGV.controlChartUCL2Value length = 8

format = 14.8

informat = 14.8

label = 'controlChartUCL2Value',

W1B0AGV.controlChartUCLMRValue length = 8

format = 14.8

informat = 14.8

label = 'controlChartUCLMRValue',

W1B0AGV.EWMAPredicationZt length = 8

format = 14.8

informat = 14.8

label = 'EWMAPredicationZt',

W1B0AGV.EWMAResidualet length = 8

format = 14.8

informat = 14.8

label = 'EWMAResidualet',

W1B0AGV.EWMAUCL1Value length = 8

format = 14.8

informat = 14.8

label = 'EWMAUCL1Value',

W1B0AGV.EWMAUCL2Value length = 8

format = 14.8

informat = 14.8

label = 'EWMAUCL2Value',

W1B0AGV.HLM4WkTotal length = 8

format = 14.8

informat = 14.8

label = 'HLM4WkTotal',

W1B0AGV.HLMMean length = 8

format = 14.8

informat = 14.8

label = 'HLMMean',

W1B0AGV.HLMSD length = 8

format = 14.8

informat = 14.8

label = 'HLMSD',

W1B0AGV.HLMUCL length = 8

format = 14.8

informat = 14.8

label = 'HLMUCL',

W1B0AGV.rate length = 8

format = 11.6

informat = 11.6

label = 'rate',

W1B0AGV.labGroupID length = 8

format = 11.

informat = 11.

label = 'labGroupID',

W1BUEVT.syndromeGroupName length = 50

format = $50.

informat = $50.

label = 'syndromeGroupName',

W1BUEVT.syndromeGroupFullName length = 255

format = $255.

informat = $255.

label = 'syndromeGroupFullName',

W1BUEVT.effectiveDate length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'effectiveDate',

W1BUEVT.expiryDate length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'expiryDate',

W1BUEVT.displaySequence length = 8

format = 6.

informat = 6.

label = 'displaySequence',

W1BUEVT.remark length = 500

format = $500.

informat = $500.

label = 'remark',

W1BUEVT.createdBy length = 20

format = $20.

informat = $20.

label = 'createdBy',

W1BUEVT.createdTime length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'createdTime',

W1BUEVT.lastUpdatedBy length = 20

format = $20.

informat = $20.

label = 'lastUpdatedBy',

W1BUEVT.lastUpdatedTime length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'lastUpdatedTime'

from

work.W1B0AGV as W1B0AGV,

work.W1BUEVT as W1BUEVT

where

W1B0AGV.syndromeGroupID = W1BUEVT.syndromeGroupID

;

quit;

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Join **/

/*==========================================================================*

* Step: Join A5NBTOIY.BY0002HV *

* Transform: Join *

* Description: *

* *

* Source Tables: SnapshotSatScanResult_AED - A5NBTOIY.BD0000EJ *

* Sherlock.SnapshotSatScanResult_AED *

* Extract - work.W1B0AGV A5NBTOIY.C60001N0 *

* Target Table: Join - work.W1GAR6L A5NBTOIY.C60001N3 *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002HV);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

%let etls_recnt = 0;

%macro etls_recordCheck;

%let etls_recCheckExist = %eval(%sysfunc(exist(Sherlock.SnapshotSatScanResult_AED, DATA)) or

%sysfunc(exist(Sherlock.SnapshotSatScanResult_AED, VIEW)));

%if (&etls_recCheckExist) %then

%do;

proc sql noprint;

select count(*) into :etls_recnt from Sherlock.SnapshotSatScanResult_AED;

quit;

%end;

%mend etls_recordCheck;

%etls_recordCheck;

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

proc datasets lib = work nolist nowarn memtype = (data view);

delete W1GAR6L;

quit;

proc sql;

create view work.W1GAR6L as

select

SnapshotSatScanResult_AED.syndromeGroupID length = 8

format = 11.

informat = 11.

label = 'syndromeGroupID',

SnapshotSatScanResult_AED.attendanceDate length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'attendanceDate'

from

Sherlock.SnapshotSatScanResult_AED as SnapshotSatScanResult_AED,

work.W1B0AGV as W1B0AGV

where

SnapshotSatScanResult_AED.syndromeGroupID = W1B0AGV.syndromeGroupID

and SnapshotSatScanResult_AED.attendanceDate = W1B0AGV.attendanceDate

and SnapshotSatScanResult_AED.syndromeGroupID NOT IS MISSING

;

quit;

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Join **/

/*==========================================================================*

* Step: Join A5NBTOIY.BY0002HW *

* Transform: Join *

* Description: *

* *

* Source Tables: Join - work.W1GAR6L A5NBTOIY.C60001N3 *

* Join - work.W16944G A5NBTOIY.C60001MZ *

* Target Table: Join - work.W1BBHKG A5NBTOIY.C60001N4 *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002HW);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

%let etls_recnt = -1;

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

proc datasets lib = work nolist nowarn memtype = (data view);

delete W1BBHKG;

quit;

proc sql;

create view work.W1BBHKG as

select

W16944G.AttendanceDate length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'AttendanceDate',

W16944G.syndromeGroupID length = 8

format = 11.

informat = 11.

label = 'syndromeGroupID',

count(W1GAR6L.attendanceDate) as count length = 8

from

work.W16944G as W16944G left join

work.W1GAR6L as W1GAR6L

(

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

(

W1AQIJO.odate = datepart(W1G5OJK.attendanceDate)

) left join

work.W1BBHKG as W1BBHKG

(

W1G5OJK.attendanceDate = W1BBHKG.AttendanceDate

)

;

quit;

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Join **/

/*==========================================================================*

* Step: Create Table A5NBTOIY.BY0002HY *

* Transform: Create Table *

* Description: *

* *

* Source Table: Join - work.W1GMBDR A5NBTOIY.C60001N5 *

* Target Table: Create Table - work.W1GY0QH A5NBTOIY.C60001N6 *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002HY);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

%let etls_recnt = -1;

%let SYSLAST = %nrquote(work.W1GMBDR);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLCreateTable), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

proc datasets lib = work nolist nowarn memtype = (data view);

delete W1GY0QH;

quit;

proc sql

;

create view work.W1GY0QH as

select

W1GMBDR.odate length = 8

format = DATE9.

informat = DATE9.,

case when W1GMBDR.dailyEWMACusumSignal = 1 then W1GMBDR.rate end as dailyEWMACusumSignal length = 8

format = 11.6

informat = 11.6

label = 'dailyEWMACusumSignal',

W1GMBDR.sevenDayMARate as sevenDayMARate length = 8

format = 11.6

informat = 11.6

label = 'sevenDayMARate',

W1GMBDR.ccSignal length = 8

format = 1.

informat = 1.

label = 'ccSignal',

W1GMBDR.controlChartUCL2Value length = 8

format = 14.8

informat = 14.8

label = 'controlChartUCL2Value',

W1GMBDR.syndromeGroupFullName length = 255

format = $255.

informat = $255.

label = 'syndromeGroupFullName',

W1GMBDR.count length = 8,

W1GMBDR.rate as rate length = 8

format = 11.6

informat = 11.6

label = 'rate'

from

work.W1GMBDR as W1GMBDR

;

quit;

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Create Table **/

/*==========================================================================*

* Step: Delete A5NBTOIY.BY0002HZ *

* Transform: Delete *

* Description: *

* *

* Target Table: RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL *

* RPTIMAGE.RST_DAT_BGIS_108_001 *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002HZ);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

/* Access the data for RptImage */

LIBNAME RPTIMAGE BASE "\\dcdissas54v\sas\CDIS\ReportSnapshot";

%rcSet(&syslibrc);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SqlDelete), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

%macro etls_sql_delete;

/* Determine if the table exists */

%let etls_tableExist = %eval(%sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, DATA)) or

%sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, VIEW)));

/*---- Create a new table ----*/

%if (&etls_tableExist eq 0) %then

%do; /* if table does not exist */

%put %str(NOTE: Creating table ...);

data RPTIMAGE.RST_DAT_BGIS_108_001;

attrib year length = 8;

attrib month length = 8;

attrib day length = 8;

attrib odate length = 8

format = DATE9.

informat = DATE9.;

attrib Adm_date length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'Adm_date';

attrib syndromeGroupFullName length = $255

format = $255.

informat = $255.

label = 'syndromeGroupFullName';

attrib sevenDayMARate length = 8

format = 11.6

informat = 11.6

label = 'sevenDayMARate';

attrib rate length = 8

format = 11.6

informat = 11.6

label = 'rate';

attrib controlChartUCL2Value length = 8

format = 14.8

informat = 14.8

label = 'controlChartUCL2Value';

attrib dailyEWMACusumSignal length = 8

format = 11.6

informat = 11.6

label = 'dailyEWMACusumSignal';

attrib ccSignal length = 8

format = 1.

informat = 1.

label = 'ccSignal';

attrib count length = 8;

attrib snapshot_id length = $8;

attrib day2 length = 8;

call missing(of _all_);

stop;

run;

%rcSet(&syserr);

%end; /* if table does not exist */

/*---- Delete the target table ----*/

proc sql;

delete from RPTIMAGE.RST_DAT_BGIS_108_001

where

RST_DAT_BGIS_108_001.snapshot_id = "&snapshot_id"

;quit;

%rcSet(&syscc);

%mend etls_sql_delete;

/* execute etls_sql_delete */

%etls_sql_delete

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Delete **/

/*==========================================================================*

* Step: Insert Rows A5NBTOIY.BY0002I0 *

* Transform: Insert Rows *

* Description: *

* *

* Source Table: Create Table - work.W1GY0QH A5NBTOIY.C60001N6 *

* Target Table: RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL *

* RPTIMAGE.RST_DAT_BGIS_108_001 *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002I0);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

%let etls_recnt = -1;

%let SYSLAST = %nrquote(work.W1GY0QH);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLInsertRows), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

%macro etls_sql_insert_rows;

/* Determine if the table exists */

%let etls_tableExist = %eval(%sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, DATA)) or

%sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, VIEW)));

/*---- Create a new table ----*/

%if (&etls_tableExist eq 0) %then

%do; /* if table does not exist */

%put %str(NOTE: Creating table ...);

data RPTIMAGE.RST_DAT_BGIS_108_001;

attrib year length = 8;

attrib month length = 8;

attrib day length = 8;

attrib odate length = 8

format = DATE9.

informat = DATE9.;

attrib Adm_date length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'Adm_date';

attrib syndromeGroupFullName length = $255

format = $255.

informat = $255.

label = 'syndromeGroupFullName';

attrib sevenDayMARate length = 8

format = 11.6

informat = 11.6

label = 'sevenDayMARate';

attrib rate length = 8

format = 11.6

informat = 11.6

label = 'rate';

attrib controlChartUCL2Value length = 8

format = 14.8

informat = 14.8

label = 'controlChartUCL2Value';

attrib dailyEWMACusumSignal length = 8

format = 11.6

informat = 11.6

label = 'dailyEWMACusumSignal';

attrib ccSignal length = 8

format = 1.

informat = 1.

label = 'ccSignal';

attrib count length = 8;

attrib snapshot_id length = $8;

attrib day2 length = 8;

call missing(of _all_);

stop;

run;

%rcSet(&syserr);

%end; /* if table does not exist */

/*---- Insert rows into target table ----*/

proc sql;

insert into RPTIMAGE.RST_DAT_BGIS_108_001 (year, month, day, odate, Adm_date, syndromeGroupFullName, sevenDayMARate, rate, controlChartUCL2Value, dailyEWMACusumSignal, ccSignal, count, snapshot_id, day2)

select distinct

year(W1GY0QH.odate) as year length = 8,

month(W1GY0QH.odate) as month length = 8,

day(W1GY0QH.odate) as day length = 8,

W1GY0QH.odate length = 8

format = DATE9.

informat = DATE9.,

. as Adm_date length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'Adm_date',

W1GY0QH.syndromeGroupFullName length = 255

format = $255.

informat = $255.

label = 'syndromeGroupFullName',

W1GY0QH.sevenDayMARate length = 8

format = 11.6

informat = 11.6

label = 'sevenDayMARate',

W1GY0QH.rate length = 8

format = 11.6

informat = 11.6

label = 'rate',

W1GY0QH.controlChartUCL2Value length = 8

format = 14.8

informat = 14.8

label = 'controlChartUCL2Value',

W1GY0QH.dailyEWMACusumSignal length = 8

format = 11.6

informat = 11.6

label = 'dailyEWMACusumSignal',

W1GY0QH.ccSignal length = 8

format = 1.

informat = 1.

label = 'ccSignal',

W1GY0QH.count length = 8,

"&snapshot_id" as snapshot_id length = 8,

. as day2 length = 8

from

work.W1GY0QH as W1GY0QH

;

quit;

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%mend etls_sql_insert_rows;

/* execute etls_sql_insert_rows */

%etls_sql_insert_rows;

/*---- Start of Post-Process Code ----*/

data RPTIMAGE.RST_DAT_BGIS_108_001;

set RPTIMAGE.RST_DAT_BGIS_108_001;

if count=0 then count=.;

else count=count;

run;

/*---- End of Post-Process Code ----*/

%rcSet(&syserr);

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Insert Rows **/

/*---- Start of Post-Process Code ----*/

%let log_endTime = %sysfunc(datetime(),datetime21.2);

proc sql;

insert into BGIS.DIS_LOG (DIS_JOB_ID, LAST_SNAPSHOT_ID, RUN_DATE, REMARK)

values ("&etls_jobName", "&snapshot_id", "&log_endTime"dt, "");

run;

/*---- End of Post-Process Code ----*/

%rcSet(&syserr);

%rcSet(&sqlrc);

%let etls_endTime = %sysfunc(datetime(),datetime.);

/* Turn off performance statistics collection */

data _null_;

if "&_perfinit" eq "1" then

call execute('%perfend;');

run;

/** Job end DIS-JOB-BGIS-108-001 **/

/* Reset jobid and name */

%let jobID = %quote(A5NBTOIY.BV0004VJ);

%let etls_jobName = %nrquote(A_testing);

/*==========================================================================*

* Step: Set Flag to N A5NBTOIY.BY000BIW *

* Transform: User Written *

* Description: *

* *

* Target Table: User Written - work.WI1GVQG A5NBTOIY.C600087Q *

* *

* User Written: SourceCode *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY000BIW);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|UserWritten), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%let _INPUT_count = 0;

%let _OUTPUT_count = 1;

%let _OUTPUT = work.WI1GVQG;

%let _OUTPUT_connect = ;

%let _OUTPUT_engine = ;

%let _OUTPUT_memtype = DATA;

%let _OUTPUT_options = %nrquote();

%let _OUTPUT_alter = %nrquote();

%let _OUTPUT_path = %nrquote(/User Written_A5NBTOIY.C600087Q%(WorkTable%));

%let _OUTPUT_type = 1;

%let _OUTPUT_label = %nrquote();

%let _OUTPUT_col_count = 0;

%let _OUTPUT1 = work.WI1GVQG;

%let _OUTPUT1_connect = ;

%let _OUTPUT1_engine = ;

%let _OUTPUT1_memtype = DATA;

%let _OUTPUT1_options = %nrquote();

%let _OUTPUT1_alter = %nrquote();

%let _OUTPUT1_path = %nrquote(/User Written_A5NBTOIY.C600087Q%(WorkTable%));

%let _OUTPUT1_type = 1;

%let _OUTPUT1_label = %nrquote();

%let _OUTPUT1_col_count = 0;

/*---- Start of User Written Code ----*/

data SOL.REPORT_TO_DI_MAPPING;

set SOL.REPORT_TO_DI_MAPPING;

if REPORT='RPT-BGIS-022W' then FLAG='N';

run;

/*---- End of User Written Code ----*/

%rcSet(&syserr);

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Set Flag to N **/

%exitetls_conditionWH2ICMT:

%mend etls_conditionWH2ICMT;

%etls_conditionWH2ICMT;

/** Step end Conditional Start **/

/*==========================================================================*

* Step: Delete A5NBTOIY.BY000BIX *

* Transform: Delete *

* Description: *

* *

* Target Table: RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL *

* RPTIMAGE.RST_DAT_BGIS_108_001 *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY000BIX);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SqlDelete), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

%macro etls_sql_delete;

/* Determine if the table exists */

%let etls_tableExist = %eval(%sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, DATA)) or

%sysfunc(exist(RPTIMAGE.RST_DAT_BGIS_108_001, VIEW)));

/*---- Create a new table ----*/

%if (&etls_tableExist eq 0) %then

%do; /* if table does not exist */

%put %str(NOTE: Creating table ...);

data RPTIMAGE.RST_DAT_BGIS_108_001;

attrib year length = 8;

attrib month length = 8;

attrib day length = 8;

attrib odate length = 8

format = DATE9.

informat = DATE9.;

attrib Adm_date length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'Adm_date';

attrib syndromeGroupFullName length = $255

format = $255.

informat = $255.

label = 'syndromeGroupFullName';

attrib sevenDayMARate length = 8

format = 11.6

informat = 11.6

label = 'sevenDayMARate';

attrib rate length = 8

format = 11.6

informat = 11.6

label = 'rate';

attrib controlChartUCL2Value length = 8

format = 14.8

informat = 14.8

label = 'controlChartUCL2Value';

attrib dailyEWMACusumSignal length = 8

format = 11.6

informat = 11.6

label = 'dailyEWMACusumSignal';

attrib ccSignal length = 8

format = 1.

informat = 1.

label = 'ccSignal';

attrib count length = 8;

attrib snapshot_id length = $8;

attrib day2 length = 8;

call missing(of _all_);

stop;

run;

%rcSet(&syserr);

%end; /* if table does not exist */

/*---- Delete the target table ----*/

proc sql;

delete from RPTIMAGE.RST_DAT_BGIS_108_001

where

RST_DAT_BGIS_108_001.snapshot_id = '20171231'

;quit;

%rcSet(&syscc);

%mend etls_sql_delete;

/* execute etls_sql_delete */

%etls_sql_delete

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Delete **/

%let etls_endTime = %sysfunc(datetime(),datetime.);

/* Turn off performance statistics collection */

data _null_;

if "&_perfinit" eq "1" then

call execute('%perfend;');

run;

2) DIS-JOB-BGIS-108-001 Code:

/****************************************************************************

* Job: DIS-JOB-BGIS-108-001 A5NBTOIY.BV00004F *

* Description: *

* *

* Metadata Server: dcdissas53v.cdisdev.chp.hksarg *

* Port: 8561 *

* Location: /DIS/Jobs *

* *

* Server: SASApp A5NBTOIY.AS000003 *

* *

* Source Tables: DIS_LOG - BGIS.DIS_LOG A5NBTOIY.BD00010K *

* WEEK_NUMBER - SOL.WEEK_NUMBER A5NBTOIY.BD00000A *

* CalculatedResult_AED - A5NBTOIY.BD0000BO *

* Sherlock.CalculatedResult_AED *

* SyndromeGroup_AED - A5NBTOIY.BD0000F0 *

* Sherlock.SyndromeGroup_AED *

* SnapshotSatScanResult_AED - A5NBTOIY.BD0000EJ *

* Sherlock.SnapshotSatScanResult_AED *

* SatScanStatus_AED - A5NBTOIY.BD000434 *

* ShLog.SatScanStatus_AED *

* Target Tables: RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL *

* RPTIMAGE.RST_DAT_BGIS_108_001 *

* RST_DAT_BGIS_108_001 - A5NBTOIY.BD0000UL *

* RPTIMAGE.RST_DAT_BGIS_108_001 *

* *

* Generated on: 2015年03月23日星期一下午04時43分34秒 CST *

* Generated by: super2@CDISDEV *

* Version: SAS Data Integration Studio 4.9 *

****************************************************************************/

/* Generate the process id for job */

%put Process ID: &SYSJOBID;

/* General macro variables */

%let jobID = %quote(A5NBTOIY.BV00004F);

%let etls_jobName = %nrquote(DIS-JOB-BGIS-108-001);

%let etls_userID = %nrquote(super2@CDISDEV);

%global applName;

data _null_;

applName="SAS Data Integration Studio";

call symput('applName',%nrstr(applName));

run;

/* Performance Statistics require ARM_PROC sub-system */

%macro etls_startPerformanceStats;

%log4sas();

%log4sas_logger(Perf.ARM, 'level=info');

options armagent=log4sas armsubsys=(ARM_PROC);

%global _armexec;

%let _armexec = 1;

%perfinit(applname="&applName");

%global etls_recnt;

%let etls_recnt=-1;

%mend;

%etls_startPerformanceStats;

%macro etls_setArmagent;

%let armagentLength = %length(%sysfunc(getoption(armagent)));

%if (&armagentLength eq 0) %then

%do;

%log4sas();

%log4sas_logger(Perf.ARM, 'level=info');

options armagent=log4sas armsubsys=(ARM_PROC);

%end;

%mend etls_setArmagent;

%macro etls_setPerfInit;

%if "&_perfinit" eq "0" %then

%do;

%etls_setArmagent;

%global _armexec;

%let _armexec = 1;

%perfinit(applname="&applName");

%end;

%mend etls_setPerfInit;

/* Setup to capture return codes */

%global job_rc trans_rc sqlrc syscc;

%let sysrc = 0;

%let job_rc = 0;

%let trans_rc = 0;

%let sqlrc = 0;

%let syscc = 0;

%global etls_stepStartTime;

/* initialize syserr to 0 */

data _null_; run;

%macro rcSet(error);

%if (&error gt &trans_rc) %then

%let trans_rc = &error;

%if (&error gt &job_rc) %then

%let job_rc = &error;

%mend rcSet;

%macro rcSetDS(error);

if &error gt input(symget('trans_rc'),12.) then

call symput('trans_rc',trim(left(put(&error,12.))));

if &error gt input(symget('job_rc'),12.) then

call symput('job_rc',trim(left(put(&error,12.))));

%mend rcSetDS;

/* Create metadata macro variables */

%let IOMServer = %nrquote(SASApp);

%let metaPort = %nrquote(8561);

%let metaServer = %nrquote(dcdissas53v.cdisdev.chp.hksarg);

/* Set metadata options */

options metaport = &metaPort

metaserver = "&metaServer";

/* Setup for capturing job status */

%let etls_startTime = %sysfunc(datetime(),datetime.);

%let etls_recordsBefore = 0;

%let etls_recordsAfter = 0;

%let etls_lib = 0;

%let etls_table = 0;

%global etls_debug;

%macro etls_setDebug;

%if %str(&etls_debug) ne 0 %then

OPTIONS MPRINT%str(;);

%mend;

%etls_setDebug;

/*---- Start of Pre-Process Code ----*/

*%LET snapshot_id=20131231;

%put &snapshot_id.;

/*---- End of Pre-Process Code ----*/

%rcSet(&syserr);

%rcSet(&sqlrc);

/*==========================================================================*

* Step: Extract A5NBTOIY.BY0002HO *

* Transform: Extract *

* Description: *

* *

* Source Table: DIS_LOG - BGIS.DIS_LOG A5NBTOIY.BD00010K *

* Target Table: Extract - work.W1ALZ89 A5NBTOIY.C60001MW *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002HO);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

/* Access the data for CDIS_BGIS */

LIBNAME BGIS ORACLE DBSERVER_MAX_BYTES=1 DBCLIENT_MAX_BYTES=1 PATH=ANASIT0 SCHEMA=CDIS_BGIS AUTHDOMAIN="bgisdbAuth" ;

%rcSet(&syslibrc);

%let etls_recnt = 0;

%macro etls_recordCheck;

%let etls_recCheckExist = %eval(%sysfunc(exist(BGIS.DIS_LOG, DATA)) or

%sysfunc(exist(BGIS.DIS_LOG, VIEW)));

%if (&etls_recCheckExist) %then

%do;

proc sql noprint;

select count(*) into :etls_recnt from BGIS.DIS_LOG;

quit;

%end;

%mend etls_recordCheck;

%etls_recordCheck;

%let SYSLAST = %nrquote(BGIS.DIS_LOG);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

/*---- Map the columns ----*/

proc datasets lib = work nolist nowarn memtype = (data view);

delete W1ALZ89;

quit;

%put %str(NOTE: Mapping columns ...);

proc sql;

create view work.W1ALZ89 as

select

DIS_JOB_ID,

LAST_SNAPSHOT_ID,

RUN_DATE,

REMARK

from &SYSLAST

;

quit;

%let SYSLAST = work.W1ALZ89;

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Extract **/

/*==========================================================================*

* Step: Extract A5NBTOIY.BY0002HP *

* Transform: Extract *

* Description: *

* *

* Source Table: WEEK_NUMBER - SOL.WEEK_NUMBER A5NBTOIY.BD00000A *

* Target Table: Extract - work.W1AQIJO A5NBTOIY.C60001MX *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002HP);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

/* Access the data for SOLITARY */

LIBNAME SOL BASE "\\dcdissas54v\sas\CDIS\Solitary File";

%rcSet(&syslibrc);

%let etls_recCheckExist = 0;

%let etls_recnt = 0;

%macro etls_recordCheck;

%let etls_recCheckExist = %eval(%sysfunc(exist(SOL.WEEK_NUMBER, DATA)) or

%sysfunc(exist(SOL.WEEK_NUMBER, VIEW)));

%if (&etls_recCheckExist) %then

%do;

%local etls_syntaxcheck;

%let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck));

/* Turn off syntaxcheck option to perform following steps */

options nosyntaxcheck;

proc contents data = SOL.WEEK_NUMBER out = work.etls_contents(keep = nobs) noprint;

run;

data _null_;

set work.etls_contents (obs = 1);

call symput("etls_recnt", left(put(nobs,32.)));

run;

proc datasets lib = work nolist nowarn memtype = (data view);

delete etls_contents;

quit;

/* Reset syntaxcheck option to previous setting */

options &etls_syntaxcheck;

%end;

%mend etls_recordCheck;

%etls_recordCheck;

%let SYSLAST = %nrquote(SOL.WEEK_NUMBER);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

/*---- Map the columns ----*/

proc datasets lib = work nolist nowarn memtype = (data view);

delete W1AQIJO;

quit;

%put %str(NOTE: Mapping columns ...);

proc sql;

create view work.W1AQIJO as

select

odate

from &SYSLAST

where year(ODATE)>=input(substr("&SNAPSHOT_ID",1,4),BEST.)-2 and ODATE<=input("&SNAPSHOT_ID",yymmdd8.)-1

;

quit;

%let SYSLAST = work.W1AQIJO;

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Extract **/

/*==========================================================================*

* Step: Join A5NBTOIY.BY0002HR *

* Transform: Join *

* Description: *

* *

* Source Table: SatScanStatus_AED - A5NBTOIY.BD000434 *

* ShLog.SatScanStatus_AED *

* Target Table: Join - work.W16944G A5NBTOIY.C60001MZ *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002HR);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

/* Access the data for Sherlock_Log */

LIBNAME ShLog ODBC DATASRC=sherlock_log_chp SCHEMA=dbo AUTHDOMAIN="SherlLogCHPAuth" ;

%rcSet(&syslibrc);

%let etls_recnt = 0;

%macro etls_recordCheck;

%let etls_recCheckExist = %eval(%sysfunc(exist(ShLog.SatScanStatus_AED, DATA)) or

%sysfunc(exist(ShLog.SatScanStatus_AED, VIEW)));

%if (&etls_recCheckExist) %then

%do;

proc sql noprint;

select count(*) into :etls_recnt from ShLog.SatScanStatus_AED;

quit;

%end;

%mend etls_recordCheck;

%etls_recordCheck;

%let SYSLAST = %nrquote(ShLog.SatScanStatus_AED);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SQLJoin), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

proc datasets lib = work nolist nowarn memtype = (data view);

delete W16944G;

quit;

proc sql;

create view work.W16944G as

select

SatScanStatus_AED.attendanceDate length = 8

format = DATETIME22.3

informat = DATETIME22.3

label = 'AttendanceDate',

SatScanStatus_AED.syndromeGroupID length = 8

format = 11.

informat = 11.

label = 'syndromeGroupID'

from

ShLog.SatScanStatus_AED as SatScanStatus_AED

where

SatScanStatus_AED.complete = 1

;

quit;

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));

%let etls_recnt=-1;

/** Step end Join **/

/*==========================================================================*

* Step: Extract A5NBTOIY.BY0002HS *

* Transform: Extract *

* Description: *

* *

* Source Table: CalculatedResult_AED - A5NBTOIY.BD0000BO *

* Sherlock.CalculatedResult_AED *

* Target Table: Extract - work.W1B0AGV A5NBTOIY.C60001N0 *

*==========================================================================*/

%let transformID = %quote(A5NBTOIY.BY0002HS);

%let trans_rc = 0;

%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

/* Access the data for Sherlock */

LIBNAME Sherlock ODBC DATASRC=sherlock_chp SCHEMA=dbo AUTHDOMAIN="sherlCHPAuth" ;

%rcSet(&syslibrc);

%let etls_recnt = 0;

%macro etls_recordCheck;

%let etls_recCheckExist = %eval(%sysfunc(exist(Sherlock.CalculatedResult_AED, DATA)) or

%sysfunc(exist(Sherlock.CalculatedResult_AED, VIEW)));

%if (&etls_recCheckExist) %then

%do;

proc sql noprint;

select count(*) into :etls_recnt from Sherlock.CalculatedResult_AED;

quit;

%end;

%mend etls_recordCheck;

%etls_recordCheck;

%let SYSLAST = %nrquote(Sherlock.CalculatedResult_AED);

/* Runtime statistics macros */

%etls_setPerfInit;

%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ;

%global etls_sql_pushDown;

%let etls_sql_pushDown = -1;

option DBIDIRECTEXEC;

/*---- Map the columns ----*/

proc datasets lib = work nolist nowarn memtype = (data view);

delete W1B0AGV;

quit;

data _null_;

put "NOTE: The following column(s) do not have a column mapping, so the"

" value(s) will be set to missing: labGroupID";

run;

%put %str(NOTE: Mapping columns ...);

proc sql;

create view work.W1B0AGV as

select

attendanceDate,

spatialLevelID,

groupedCases,

dailyCusum

format = 11.6

informat = 11.6,

dailyEWMACusumSignal,

dailyCumulativePeriod,

dailyPrediction

format = 11.6

informat = 11.6,

sevenDayMARate

format = 11.6

informat = 11.6,

ccSignal,

resultType,

measurementUnit,

year,

weekNum,

weekEnding,

month,

syndromeGroupID,

sevenDayMACount

format = 14.8

informat = 14.8,

controlChartUCL1,

controlChartUCL2,

controlChartUCLMR,

EWMAUCL1,

EWMAUCL2,

HLM,

controlChartUCL1Value

format = 14.8

informat = 14.8,

controlChartUCL2Value

format = 14.8

informat = 14.8,

controlChartUCLMRValue

format = 14.8

informat = 14.8,

EWMAPredicationZt

format = 14.8

informat = 14.8,

EWMAResidualet

format = 14.8

informat = 14.8,

EWMAUCL1Value

format = 14.8

informat = 14.8,

EWMAUCL2Value

format = 14.8

informat = 14.8,

HLM4WkTotal

format = 14.8

informat = 14.8,

HLMMean

format = 14.8

informat = 14.8,

HLMSD

format = 14.8

informat = 14.8,

HLMUCL

format = 14.8

informat = 14.8,

rate

format = 11.6

informat = 11.6,

. as labGroupID length = 8

format = 11.

informat = 11.

label = 'labGroupID'

from &SYSLAST

;

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

(

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

(

W1AQIJO.odate = datepart(W1G5OJK.attendanceDate)

) left join

work.W1BBHKG as W1BBHKG

(

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;

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;

Re: Passing a macro variable in to a subjob

gergely_batho — Mon, 23 Mar 2015 09:34:02 GMT

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ó

Re: Passing a macro variable in to a subjob

Patrick — Mon, 23 Mar 2015 10:58:00 GMT

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.