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

* Job: PNCHCTPF9000_QLD_PMOV_Extract A5K35U44.BQ000A4Y *

* Description: Create a one off Weekly and Daily data ready for daily *
* increment process. *
* *
* Metadata Server: SASAP5 *
* Port: 8564 *
* Location: /IDW/PROTECT_NICHE_CTP/Jobs/Actuarial Base Layer *
* Jobs/Full Load Monthly Process Jobs/PNCHCTP_SUN_QLD *
* *
* Server: SASApp A5K35U44.AR000002 *
* *
* User Written: PNCHPIF9000_PMOV_SETUP *
* Create a one off data in Daily, Weekly and Monthly *
* PMOV from HIST Library *
* *
* Generated on: Tuesday, 13 May 2014 5:53:50 PM EST *
* Generated by: devschd *
* Version: SAS Data Integration Studio 4.4 *
****************************************************************************/
/* General macro variables */
%let jobID = %quote(A5K35U44.BQ000A4Y);
%let etls_jobName = %nrquote(PNCHCTPF9000_QLD_PMOV_Extract);
%let etls_userID = %nrquote(devschd);
/* Setup to capture return codes */
%global job_rc trans_rc sqlrc;
%let sysrc = 0;
%let job_rc = 0;
%let trans_rc = 0;
%let sqlrc = 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(8564);
%let metaServer = %nrquote(SASAP5);
/* 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 ----*/
********** Pre Process job for Base Layer ****************;

DATA _NULL_;
CALL SYMPUT('env', "%resolveenv(env)");
CALL SYMPUT('envSPDS', "%resolveenv(envSPDS)");
CALL SYMPUT('envJV', "%resolveenv(envJV)");
CALL SYMPUT('mdpath', "%resolveenv(mdpath)");
RUN;

********** Library Definition **************;
LIBNAME spdswork SASSPDS GROUP="&envSPDS." user="&uid." passwd = "&pw." schema
="SPDSWORK" server=&spdshost..5090 TEMP=YES ;
LIBNAME mbpncpsh SASSPDS GROUP="&envSPDS." user="&uid." passwd = "&pw." schema
="M_B_PNCH_CTP_STAGING_HIST" server=&spdshost..5090;
LIBNAME dbpncstg SASSPDS GROUP="&envSPDS." user="&uid." passwd = "&pw." schema
="D_B_PNCH_STAGING" server=&spdshost..5090;
LIBNAME mbnchpph SASSPDS GROUP="&envSPDS." user="&uid." passwd = "&pw." schema
="M_B_PNCH_CTP_PRELIVE_HIST" server=&spdshost..5090;
LIBNAME D_S_PNCH SASSPDS GROUP="&envSPDS." user="&uid." passwd = "&pw." schema
="D_S_PNCH" server=&spdshost..5090;
LIBNAME mapnchpl SASSPDS GROUP="&envSPDS." user="&uid." passwd = "&pw." schema
="M_A_PNCH_PRELIVE" server=&spdshost..5090;
LIBNAME dbpnch SASSPDS GROUP="&envSPDS." user="&uid." passwd = "&pw." schema
="D_B_PNCH_CTP" server=&spdshost..5090;

/* Access the data for Daily Protect Niche CTP Source Layer Data */
LIBNAME dspnctp SASSPDS GROUP="&envSPDS." user="&uid." passwd = "&pw." SC
HEMA="D_S_PNCH_CTP" server=&spdshost..5090;


LIBNAME LIBRARY "&mdpath.SASFormats/PROTECT_NICHE";
LIBNAME LIBRARY1 "&mdpath.SASFormats/PROTECT_AAMI";

OPTIONS FULLSTIMER MSGLEVEL=I;
%LET COMPRESS = YES;
%LET SPDSDCMP = YES;
%LET SPDSIASY = YES;

/* To parameterise some of the UWC */
%LET pnchpl = mbpnchpl;

%LET pnchstg = MBPNCPSH;
%LET dpnchstg = dbpncstg;
%LET pnchsrc = d_s_pnch;
*%LET dbpnch = dbpnch;
%LET mapnchpl = mapnchpl;

*** recoveryExcess per company ***;
%LET recoveryExcess1 = '01';
%LET recoveryExcess2 = '01';
%LET recoveryExcess3 = '01','09','18','33','50','51','52','53','54','55','58','5
9','60','62','63','64','65',
'81','85','86','87';
%LET recoveryExcess4 = '01';
%LET recoveryExcess5 = '01','39','40','85','86','87','41';
%LET recoveryExcess6 = '01','09','18','33','51','52','53','54','55','56','57','5
9','60','61','62','63','65','73';
%LET recoveryExcess7 = '01';
%LET recoveryExcess8 = '01';

%LET prod3 = "HDH","HAH","HLH","HDS","HAS","HDA","HAA","HDI","HAI","RSH","RSX","
RSE","RSL","PMV","PMH","PMS","PMU","PMI","RSP","RSM" ;
%LET prod4 = 'API', 'APL', 'APP', 'APV', 'AST', 'HAD' ;
%LET prod5 = "HDH","HAH","HLH","HDS","HAS","HDA","HAA","HDI","HAI","RSH","RSX","
RSE","RSL","PMV","PMH","PMS","PMU","PMI","RSP","RSM" ;


*** Gl component keep variables ***;
%LET keepoglentity = trandate level company subcoy state branch product class su
bclass agtacctyp rep _activfrom _activto OGLENTITY;
%LET keepoglcentre = trandate level company subcoy state branch product class su
bclass agtacctyp rep _activfrom _activto OGLCENTRE;
%LET keepoglcatgory = trandate level company subcoy state branch product class s
ubclass agtacctyp rep _activfrom _activto OGLCATGORY;
%LET keepoglstate = trandate level company subcoy state branch product class sub
class agtacctyp rep _activfrom _activto OGLSTATE;
%LET keepoglproduct = trandate level company subcoy state branch product class s
ubclass agtacctyp rep _activfrom _activto OGLPRODUCT;
%LET keepoglclass = trandate level company subcoy state branch product class sub
class agtacctyp rep _activfrom _activto OGLCLASS;
%LET keepoglchannel = trandate level company subcoy state branch product class s
ubclass agtacctyp rep _activfrom _activto OGLCHANNEL;
%LET keepoglproject = trandate level company subcoy state branch product class s
ubclass agtacctyp rep _activfrom _activto OGLPROJECT;
%LET keepoglintrcoy = trandate level company subcoy state branch product class s
ubclass agtacctyp rep _activfrom _activto OGLINTRCOY;
%LET keepoglspare = trandate level company subcoy state branch product class sub
class agtacctyp rep _activfrom _activto OGLSPARE;

* Define Dates macro variables;
%INCLUDE "/data/&env.data/BASE/macros/autodate.sas";

*** if you rerun CF for whatever reason after the 10th of the month ...;
*** you will have to modify the runday macro variable for finshdt to refer to th
e previous month ***;
%LET runday = 10;
%GLOBAL finshdt;
DATA _NULL_;
IF DAY("&SYSDATE."d) > &runday. THEN CALL SYMPUT('finshdt',INTNX('MONTH',"&SY
SDATE."d,0,'E'));
ELSE CALL SYMPUT('finshdt',INTNX('MONTH',"&SYSDATE."d,-1,'E'));
RUN;

/* macro to convert date fields into SAS Date fomat */
%MACRO convertdate(_indate,_outdate);
&_outdate. = INPUT(PUT(&_indate,8.) ,?? YYMMDD8.);
%MEND;

/* DI studio generate codes that are longer than 256 chars might cause problem s
o substitute with macro instead */
%LET caseexp_modified = CASE WHEN (INTCK('DAY',covRenewedDate,covChgEffDate) IN
(365,366) AND itemAtrnType = 'CN') THEN (modified + 4)
WHEN (INTCK('DAY',covRenewedDate,covChgEffDate) IN (365,366) AND itemAtrnTy
pe = 'LP') THEN (modified + 2)
WHEN (INTCK('DAY',covRenewedDate,covChgEffDate) IN (365,366) AND itemAtrnTy
pe = ' ') THEN (modified + 32)
ELSE modified END ;
/*---- End of Pre-Process Code ----*/
%rcSet(&syserr);
%rcSet(&sqlrc);
/* Create a copy of PMOV in Daily, Weekly and Monthly Library after the full loa
d Actuarial Base Layer was run */
OPTIONS FULLSTIMER MSGLEVEL=I;
%LET COMPRESS = YES;
%LET SPDSDCMP = YES;
%LET SPDSIASY = YES;
%MACRO libdef;
LIBNAME hbpnchpl SASSPDS GROUP="&envSPDS." user="&uid" passwd = "&
pw" schema="M_B_PNCH_QLD_CTP_PRELIVE_HIST" server=&spdshost..5090;
LIBNAME hbpnch SASSPDS GROUP="&envSPDS." user="&uid" passwd = "&
pw" schema="M_B_PNCH_QLD_CTP_HIST" server=&spdshost..5090;
LIBNAME hbpncstg SASSPDS GROUP="&envSPDS." user="&uid" passwd = "&
pw" schema="M_B_PNCH_QLD_CTP_STAGING_HIST" server=&spdshost..5090;
LIBNAME mbpnch SASSPDS GROUP="&envSPDS." user="&uid" passwd = "&
pw" schema="M_B_PNCH_QLD_CTP" server=&spdshost..5090;
LIBNAME mbpnchpl SASSPDS GROUP="&envSPDS." user="&uid" passwd = "&
pw" schema="M_B_PNCH_QLD_CTP_PRELIVE" server=&spdshost..5090;
LIBNAME wbpnch SASSPDS GROUP="&envSPDS." user="&uid" passwd = "&
pw" schema="W_B_PNCH_QLD_CTP" server=&spdshost..5090;
LIBNAME wbpnchpl SASSPDS GROUP="&envSPDS." user="&uid" passwd = "&
pw" schema="W_B_PNCH_QLD_CTP_PRELIVE" server=&spdshost..5090;
LIBNAME dbpnch SASSPDS GROUP="&envSPDS." user="&uid" passwd = "&
pw" schema="D_B_PNCH_QLD_CTP" server=&spdshost..5090;
LIBNAME dbpnchpl SASSPDS GROUP="&envSPDS." user="&uid" passwd = "&
pw" schema="D_B_PNCH_QLD_CTP_PRELIVE" server=&spdshost..5090;
LIBNAME dbnchpis SASSPDS GROUP="&envSPDS." user="&uid" passwd = "&pw" s
chema="D_B_PNCH_QLD_CTP_STAGING" server=&spdshost..5090 ;
LIBNAME pnchfmt "&mdpath./SASFormats/PROTECT_NICHE";
%MEND;
/* Clean up library */
%MACRO cleanup (_lib);
PROC DATASETS LIBRARY=&_lib. NOLIST;
DELETE PMOV_QLD_CTP;
QUIT;
%MEND;
%MACRO mainCopy;
%LET db = dbpnch;
%LET wb = wbpnch;
%LET mb = mbpnch;
%LET hb = hbpnch;
%LET hbpl = hbpnchpl;
%LET hbst = hbpncstg;
%LET dbst = dbnchpis;
%LET mbpl = mbpnchpl;
/*PRELIVE to LIVE HISTORY*/
PROC COPY IN=&hbpl. OUT=&hb. INDEX=YES;
SELECT PMOV_QLD_CTP MASTERKEY_QLD_CTP;
RUN;
PROC DATASETS LIB = &hb. NOLIST;
MODIFY PMOV_QLD_CTP;
INDEX DELETE _ALL_;
index create key1 = (coverageKey row_insert_dt)/ unique nomiss ;
index create policyno ;
QUIT;
/*HISTORY TO MONTHLY 8 years of data */
PROC COPY IN=&hb. OUT=&mb. INDEX=YES;
SELECT PMOV_QLD_CTP MASTERKEY_QLD_CTP;
RUN;
PROC COPY IN=&hb. OUT=&mbpl. INDEX=YES;
SELECT PMOV_QLD_CTP ;
RUN;
/*HISTORY to DAILY 2 weeks data*/
proc sort data=&hb..PMOV_QLD_CTP(WHERE=(covRenewedDate >= INTNX('WEEK',"&SYSDATE
."d,-2)))
out=&db..PMOV_QLD_CTP ;
BY company policyno product class subclass itemno trancount;
run;
PROC DATASETS LIB = &db. NOLIST;
MODIFY PMOV_QLD_CTP;
index delete _all_;
index create coverageKey / unique nomiss ;
index create policyno;
QUIT;
/*
proc sort data=&hbst..covdtls_pi(WHERE=(covRenewedDate >= INTNX('WEEK',"&SYSDATE
."d,-2)))
out=&dbst..covdtls_pi ;
BY company policy product class subclass itemno trancount;
run;
PROC DATASETS LIB = &dbst. NOLIST;
MODIFY covdtls_pi;
index create coverageKey ;
QUIT;
*/
/*HISTORY to WEEKLY 2 years of data*/
proc sort data=&hb..PMOV_QLD_CTP(WHERE=(covRenewedDate >= INTNX('YEAR',"&SYSDATE
."d,-2)))
out=&wb..PMOV_QLD_CTP ;
BY company policyno product class subclass itemno trancount;
run;
PROC DATASETS LIB = &wb. NOLIST;
MODIFY PMOV_QLD_CTP;
index delete _all_;
index create coverageKey / unique nomiss ;
index create policyno;
QUIT;
%MEND;
%libdef;
*%cleanup(mbpnchpl); /* Monthly Library Prelive */
%cleanup(hbpnch); /* History Library */
*%cleanup(dbpncstg); /* Daily Staging Library */
*%cleanup(dbpnch); /* Daily Library */
%cleanup(dbpnchpl); /* Daily Library Prelive */
*%cleanup(wbpncstg); /* Weekly Staging Library */
*%cleanup(wbpnch); /* Weeky Library */
%cleanup(wbpnchpl); /* Weekly Library Prelive */
%mainCopy;
%rcSet(&syserr);
%rcSet(&sqlrc);
%let etls_endTime = %sysfunc(datetime(),datetime.);