Untitled

avatar
unknown
plain_text
2 months ago
15 kB
9
Indexable
%MACRO DATAPULL(COUNTRY,CTR);

/* Premium detaills getting pulled up*/
DATA &COUNTRY._PREMIUM;
SET PRODLIB.WE_TPOLPRM_&COUNTRY.(KEEP=POLICY_NO RENL_CERT_NO EFF_DT_SEQ_NO COVERAGE_CD SCALC_PREM_AMT OCC_LIAB_AMT LOCATION_NO SECT_OBJ_NO RISK_OBJ_NO COV_OBJ_NO SECTION_CD RISK_CD);
IF SUBSTR(PUT(EFF_DT_SEQ_NO,10.),1,4)>=2019 AND SUBSTR(PUT(EFF_DT_SEQ_NO,10.),1,4)<2026;
EFF_DT=INPUT(SUBSTR(PUT(eff_dt_seq_no,10.),1,8),8.);
KEY=COMPRESS(POLICY_NO||RENL_CERT_NO||EFF_DT_SEQ_NO);
KEYCOV=COMPRESS(POLICY_NO||RENL_CERT_NO||EFF_DT_SEQ_NO||COVERAGE_CD);
KEYDED=COMPRESS(POLICY_NO||RENL_CERT_NO||EFF_DT_SEQ_NO||LOCATION_NO||SECT_OBJ_NO||RISK_OBJ_NO);
KEYCOMM=COMPRESS(POLICY_NO||RENL_CERT_NO||EFF_DT);
KEYMMCP=COMPRESS(POLICY_NO||RENL_CERT_NO||EFF_DT_SEQ_NO||SECT_OBJ_NO||RISK_OBJ_NO||COV_OBJ_NO||SECTION_CD||RISK_CD||COVERAGE_CD);
IF COMPRESS(COVERAGE_CD) <> "";
/*IF COMPRESS(POLICY_NO) IN ("HK22000049","0000033864","HK22011677");*/
RUN;

/* MMCP details getting pulled up to map Property, Liability & WC*/
DATA MMCP;
SET PRODLIB.WE_TCOVGOBJ_&COUNTRY.(KEEP=POLICY_NO RENL_CERT_NO EFF_DT_SEQ_NO MAJOR_LINE_CD MINOR_LINE_CD CLASS_PERIL_CD COVERAGE_CD COVG_OCC_LIAB_AMTRISK_OBJ_NO COV_OBJ_NO SECTION_CD RISK_CD SECT_OBJ_NO);
IF SUBSTR(PUT(EFF_DT_SEQ_NO,10.),1,4)>=2019 AND SUBSTR(PUT(EFF_DT_SEQ_NO,10.),1,4)<2026;
KEYMMCP=COMPRESS(POLICY_NO||RENL_CERT_NO||EFF_DT_SEQ_NO||SECT_OBJ_NO||RISK_OBJ_NO||COV_OBJ_NO||SECTION_CD||RISK_CD||COVERAGE_CD);
/*IF COMPRESS(POLICY_NO) IN ("HK22000049","0000033864","HK22011677");*/
RUN;

PROC SORT DATA=MMCP NODUP;
BY _ALL_;RUN;

PROC SQL;
CREATE TABLE &COUNTRY._PREMIUM1 AS 
SELECT A.*,
B.MAJOR_LINE_CD,
B.MINOR_LINE_CD,
B.CLASS_PERIL_CD,
COMPRESS(MAJOR_LINE_CD||MINOR_LINE_CD||CLASS_PERIL_CD) AS MMCP
FROM &COUNTRY._PREMIUM A
LEFT JOIN MMCP B ON (A.KEYMMCP=B.KEYMMCP);
QUIT;

PROC SQL;
CREATE TABLE &COUNTRY._PREMIUM2 AS 
SELECT A.*,
B.CLASS
FROM &COUNTRY._PREMIUM1 A
LEFT JOIN LOOK.PACKAGE_MMCP B ON COMPRESS(A.MMCP)=COMPRESS(B.MMCP)
;
QUIT;


/*Currency, Insured name, share% details getting pulled*/

DATA &COUNTRY._DATE(DROP=POL_INCEPT_DATE POL_EXP_DATE);
SET PRODLIB.WE_TPOLICY_&COUNTRY.(KEEP= POLICY_NO RENL_CERT_NO EFF_DT_SEQ_NO POL_INCEPT_DATE POL_EXP_DATE NATURE_OF_BUS CURRENCY_CD INSURED_NAME RENEWAL_FG POL_SIGND_LINE_PCT);
IF YEAR(DATEPART(POL_INCEPT_DATE))>=2019 AND YEAR(DATEPART(POL_INCEPT_DATE))<2026;
FORMAT REN_EFF_DT MMDDYY10.;
FORMAT REN_EXP_DT MMDDYY10.;
REN_EFF_DT=DATEPART(POL_INCEPT_DATE);
REN_EXP_DT=DATEPART(POL_EXP_DATE);
POL_SIGND_LINE_PCT=POL_SIGND_LINE_PCT/100;
IF COMPRESS(NATURE_OF_BUS) = "" THEN DELETE;
KEY=COMPRESS(POLICY_NO||RENL_CERT_NO||EFF_DT_SEQ_NO);
/*IF COMPRESS(POLICY_NO) IN ("HK22000049","0000033864","HK22011677");*/
RUN;

PROC SQL;
CREATE TABLE &COUNTRY._COMBINED_1 AS 
SELECT A.*,
B.INSURED_NAME,
B.RENEWAL_FG,
B.POL_SIGND_LINE_PCT,
B.CURRENCY_CD,
B.REN_EFF_DT,
B.REN_EXP_DT,
B.NATURE_OF_BUS
FROM &COUNTRY._PREMIUM2 A
LEFT JOIN &COUNTRY._DATE B ON COMPRESS(A.KEY)=COMPRESS(B.KEY)
WHERE REN_EFF_DT <>. AND REN_EFF_DT>=&Data_Scope_Start. AND REN_EFF_DT<=&Data_Scope_End.
;
RUN;

/* Policy Cancellation details getting pulled*/

DATA &COUNTRY._CANCEL;
SET PRODLIB.WE_TPOLKEY_&COUNTRY.;
IF SUBSTR(PUT(EFF_DT_SEQ_NO,10.),1,4)>=2019 AND SUBSTR(PUT(EFF_DT_SEQ_NO,10.),1,4)<2026;
KEY=COMPRESS(POLICY_NO||RENL_CERT_NO||EFF_DT_SEQ_NO);
/*IF COMPRESS(POLICY_NO) IN ("HK22000049","0000033864","HK22011677");*/
RUN;

PROC SQL;
CREATE TABLE &COUNTRY._CANCEL1 AS 
SELECT A.POLICY_NO,
A.RENL_CERT_NO,
A.EFF_DT_SEQ_NO,
A.record_type_cd AS RECORD_TYPE_CD,
B.REN_EFF_DT,
B.REN_EXP_DT
FROM &COUNTRY._CANCEL A
LEFT JOIN &COUNTRY._DATE B ON COMPRESS(A.KEY)=COMPRESS(B.KEY)
;
RUN;

PROC SORT DATA=&COUNTRY._CANCEL1 OUT=&COUNTRY._LIVE_CANCEL;
BY POLICY_NO REN_EFF_DT EFF_DT_SEQ_NO;
RUN;

DATA &COUNTRY._LIVE_CANCEL_COMB;
SET &COUNTRY._LIVE_CANCEL;
BY POLICY_NO EFF_DT_SEQ_NO;
IF LAST.EFF_DT_SEQ_NO;
RUN;


PROC SQL;
CREATE TABLE &COUNTRY._COMBINED_2 AS 
SELECT A.*,
B.RECORD_TYPE_CD
FROM &COUNTRY._COMBINED_1 A
LEFT JOIN &COUNTRY._LIVE_CANCEL_COMB B ON (A.POLICY_NO=B.POLICY_NO AND A.REN_EFF_DT=B.REN_EFF_DT AND A.renl_cert_no=B.renl_cert_no AND A.eff_dt_seq_no=B.eff_dt_seq_no)
;
QUIT;


/* Currency codewise currency name getting pulled*/

PROC SQL;
CREATE TABLE WD_CURRENCY AS 
SELECT DISTINCT CURR_CODE_CD,CURR_CODE_NM
FROM PRODLIB.WD_CURRENCY
;
QUIT;

PROC SORT DATA=WD_CURRENCY NODUPKEY;
BY CURR_CODE_CD;
RUN;

PROC SQL;
CREATE TABLE DATA.&COUNTRY._COMBINED_POL AS 
SELECT A.*,
C.CURR_CODE_NM,
B.CLASS
FROM &COUNTRY._COMBINED_2 A
LEFT JOIN WD_CURRENCY C ON (A.currency_cd)=(C.CURR_CODE_CD)
LEFT JOIN LOOK.PACKAGE_MMCP B ON COMPRESS(A.MMCP)=COMPRESS(B.MMCP)
;
QUIT;

PROC DATASETS LIB=WORK NOLIST;
DELETE &COUNTRY._PREMIUM
&COUNTRY._DATE 
&COUNTRY._COMBINED_1 
&COUNTRY._LIVE_CANCEL 
&COUNTRY._LIVE_CANCEL_COMB 
&COUNTRY._CANCEL1 
&COUNTRY._CANCEL 
&COUNTRY._COMBINED_1 
MMCP 
POL_DEDUCTIBLE 
&COUNTRY._COMBINED_2 
WD_CURRENCY 
&COUNTRY._PREMIUM 
&COUNTRY._PREMIUM1 
POL_COMMISSION 
WC_&COUNTRY._WAGES_3 
&COUNTRY._PREMIUM2 
WC_&COUNTRY._WAGES_11 
WC_&COUNTRY._WAGES 
WC_&COUNTRY._WAGES_1 
WC_WAGES_&COUNTRY.
;RUN;

DATA &COUNTRY._PROPERTY;
SET DATA.&COUNTRY._COMBINED_POL;
IF CLASS="Property";
RUN;

/* Deductibles details getting pulled*/

DATA POL_DEDUCTIBLE;
SET PRODLIB.WE_TPOLDED_&COUNTRY.(KEEP=POLICY_NO RENL_CERT_NO EFF_DT_SEQ_NO DED_VALUE LOCATION_NO SECT_OBJ_NO RISK_OBJ_NO COV_OBJ_NO);
IF SUBSTR(PUT(EFF_DT_SEQ_NO,10.),1,4)>=2019 AND SUBSTR(PUT(EFF_DT_SEQ_NO,10.),1,4)<2026; 
KEY=COMPRESS(POLICY_NO||RENL_CERT_NO||EFF_DT_SEQ_NO);
KEYDED=COMPRESS(POLICY_NO||RENL_CERT_NO||EFF_DT_SEQ_NO||LOCATION_NO||SECT_OBJ_NO||RISK_OBJ_NO);
/*IF COMPRESS(POLICY_NO) IN ("HK22000049","0000033864","HK22011677");*/
RUN;


PROC SQL;
CREATE TABLE &COUNTRY._PROPERTY1 AS
SELECT
A.POLICY_NO,
A.REN_EFF_DT,
A.REN_EXP_DT,
A.INSURED_NAME,
A.MAJOR_LINE_CD,
A.MINOR_LINE_CD,
A.CLASS_PERIL_CD,
A.sect_obj_no,
A.risk_obj_no,
A.cov_obj_no,
/*A.product_cd, */
A.section_cd,
A.risk_cd,
A.coverage_cd,
A.MMCP,
A.COVERAGE_CD,
A.POL_SIGND_LINE_PCT,
/*A.PRDR_COMM_VALUE,*/
A.CURR_CODE_NM,
A.SCALC_PREM_AMT,
A.OCC_LIAB_AMT,
A.CLASS,
A.RECORD_TYPE_CD,
B.DED_VALUE
FROM &COUNTRY._PROPERTY A 
LEFT JOIN POL_DEDUCTIBLE B ON (A.POLICY_NO=B.POLICY_NO AND A.RENL_CERT_NO=B.RENL_CERT_NO AND A.EFF_DT_SEQ_NO=B.EFF_DT_SEQ_NO AND A.LOCATION_NO=B.LOCATION_NO 
AND A.SECT_OBJ_NO=B.SECT_OBJ_NO AND A.RISK_OBJ_NO=B.RISK_OBJ_NO AND A.COV_OBJ_NO=B.COV_OBJ_NO)
;
QUIT;

DATA &COUNTRY._PROPERTY2;
SET &COUNTRY._PROPERTY1;
IF SCALC_PREM_AMT=0 THEN OCC_LIAB_AMT=0;
IF SCALC_PREM_AMT<0 THEN OCC_LIAB_AMT=OCC_LIAB_AMT*-1;

IF SCALC_PREM_AMT=0 THEN DED_VALUE=0;
IF SCALC_PREM_AMT<0 THEN DED_VALUE=DED_VALUE*-1;
KEY_GROUP=COMPRESS(POLICY_NO||"-"||Class||"-"||YEAR(REN_EFF_DT)||"-"||MONTH(REN_EFF_DT)||"-"||DAY(REN_EFF_DT)||"-"||risk_cd);
RUN;


PROC SQL;
CREATE TABLE &COUNTRY._PROPERTY3 AS
SELECT
A.POLICY_NO,
A.REN_EFF_DT,
A.REN_EXP_DT,
A.INSURED_NAME,
A.MAJOR_LINE_CD,
A.MINOR_LINE_CD,
A.CLASS_PERIL_CD,
A.MMCP,
A.sect_obj_no,
A.risk_obj_no,
A.cov_obj_no,
/*A.product_cd, */
A.section_cd,
A.risk_cd,
A.coverage_cd,
A.COVERAGE_CD,
A.POL_SIGND_LINE_PCT,
/*A.PRDR_COMM_VALUE,*/
A.CURR_CODE_NM,
A.CLASS,
A.RECORD_TYPE_CD,
A.KEY_GROUP,
A.OCC_LIAB_AMT,
SUM(A.SCALC_PREM_AMT) AS GWP,
SUM(A.DED_VALUE) AS DEDUCTIBLE

FROM &COUNTRY._PROPERTY2 A 
GROUP BY 1,2,3
ORDER BY KEY_GROUP
;
QUIT;

DATA &COUNTRY._PROPERTY4(DROP=KEY_GROUP);
SET &COUNTRY._PROPERTY3;
BY KEY_GROUP;
IF FIRST.KEY_GROUP;
RUN;

PROC SQL;
CREATE TABLE &COUNTRY._PROPERTY5 AS 
SELECT 
A.POLICY_NO,
A.REN_EFF_DT,
A.REN_EXP_DT,
A.INSURED_NAME,
A.MAJOR_LINE_CD,
A.MINOR_LINE_CD,
A.CLASS_PERIL_CD,
A.MMCP,
A.sect_obj_no,
A.risk_obj_no,
A.cov_obj_no,
/*A.product_cd, */
A.section_cd,
A.risk_cd,
A.coverage_cd,
A.COVERAGE_CD,
A.POL_SIGND_LINE_PCT,
/*A.PRDR_COMM_VALUE,*/
A.CURR_CODE_NM,
A.CLASS,
A.RECORD_TYPE_CD,
CATX(POLICY_NO,"-",Class,"-",YEAR(REN_EFF_DT),"-",MONTH(REN_EFF_DT),"-",DAY(REN_EFF_DT)) AS KEY_GROUP,
SUM(A.OCC_LIAB_AMT) AS LIMIT,
A.GWP,
A.DEDUCTIBLE
FROM &COUNTRY._PROPERTY4 A
GROUP BY 1,2,3
;
QUIT;

PROC SORT DATA=&COUNTRY._PROPERTY5;
BY KEY_GROUP;
RUN;

DATA &COUNTRY._PROPERTY_FINAL(DROP=KEY_GROUP);
SET &COUNTRY._PROPERTY5;
BY KEY_GROUP;
IF FIRST.KEY_GROUP;
RUN;

PROC DATASETS LIB=WORK NOLIST;
DELETE &COUNTRY._PROPERTY &COUNTRY._PROPERTY1 &COUNTRY._PROPERTY3 &COUNTRY._PROPERTY2 &COUNTRY._PROPERTY3 &COUNTRY._PROPERTY4 &COUNTRY._PROPERTY5  POL_DEDUCTIBLE;RUN;


DATA &COUNTRY._LIABILITY;
SET DATA.&COUNTRY._COMBINED_POL;
IF CLASS="Liabilities";
KEY_LIAB=COMPRESS(sect_obj_no||risk_obj_no);
KEY_GROUP=COMPRESS(POLICY_NO||"-"||Class||"-"||YEAR(REN_EFF_DT)||"-"||MONTH(REN_EFF_DT)||"-"||DAY(REN_EFF_DT));
RUN;

PROC SQL;
CREATE TABLE &COUNTRY._LIABILITY1 AS
SELECT
POLICY_NO AS POLICY_NO,
REN_EFF_DT,
REN_EXP_DT,
INSURED_NAME,
MAJOR_LINE_CD,
MINOR_LINE_CD,
CLASS_PERIL_CD,
MMCP,
SECTION_CD,
COVERAGE_CD,
POL_SIGND_LINE_PCT,
/*PRDR_COMM_VALUE,*/
CURR_CODE_NM,
SUM(SCALC_PREM_AMT) AS GWP,
occ_liab_amt AS OCC_LIAB_AMT,
/*DED_VALUE,*/
CLASS,
KEY_GROUP,
sect_obj_no AS SECT_OBJ_NO,
RISK_OBJ_NO AS RISK_OBJ_NO,
KEY_LIAB,
RECORD_TYPE_CD

FROM &COUNTRY._LIABILITY
GROUP BY KEY_GROUP
ORDER BY KEY_GROUP
;
QUIT;

PROC SORT DATA=&COUNTRY._LIABILITY1 OUT=&COUNTRY._LIABILITY2;
BY POLICY_NO REN_EFF_DT REN_EXP_DT KEY_LIAB OCC_LIAB_AMT;
RUN;

DATA &COUNTRY._LIABILITY3;
SET &COUNTRY._LIABILITY2;
BY POLICY_NO REN_EFF_DT REN_EXP_DT KEY_LIAB OCC_LIAB_AMT;
IF LAST.KEY_LIAB;
RUN;

PROC SQL;
CREATE TABLE &COUNTRY._LIABILITY4 AS
SELECT
POLICY_NO,
REN_EFF_DT,
REN_EXP_DT,
INSURED_NAME,
MAJOR_LINE_CD,
MINOR_LINE_CD,
CLASS_PERIL_CD,
MMCP,
COVERAGE_CD,
POL_SIGND_LINE_PCT,
/*PRDR_COMM_VALUE,*/
CURR_CODE_NM,
GWP,
SUM(OCC_LIAB_AMT) AS LIMIT,
/*SUM(DED_VALUE) AS DEDUCTIBLE,*/
CLASS,
RECORD_TYPE_CD,
KEY_GROUP
FROM &COUNTRY._LIABILITY3
GROUP BY 1,2,3
ORDER BY 1,2,3
;
QUIT;


PROC SORT DATA=&COUNTRY._LIABILITY4;
BY POLICY_NO REN_EFF_DT REN_EFF_DT KEY_GROUP;
RUN;

DATA &COUNTRY._LIABILITY_FINAL;
SET &COUNTRY._LIABILITY4;
BY POLICY_NO REN_EFF_DT REN_EFF_DT KEY_GROUP;
IF FIRST.KEY_GROUP;
RUN;


PROC DATASETS LIB=WORK NOLIST;
DELETE &COUNTRY._LIABILITY &COUNTRY._LIABILITY1 &COUNTRY._LIABILITY2 &COUNTRY._LIABILITY3 &COUNTRY._LIABILITY4 ;RUN;

DATA &COUNTRY._WC;
SET DATA.&COUNTRY._COMBINED_POL;
IF CLASS="Workers Comp";
KEY_GROUP=COMPRESS(POLICY_NO||"-"||Class||"-"||YEAR(REN_EFF_DT)||"-"||MONTH(REN_EFF_DT)||"-"||DAY(REN_EFF_DT));
RUN;

/*Wages for WC details getting pulled*/

DATA WC_WAGES_&COUNTRY.;
SET PRODLIB.WE_TPCASLTY_&COUNTRY.(KEEP=POLICY_NO RENL_CERT_NO EFF_DT_SEQ_NO LOCATION_NO SECT_OBJ_NO RISK_OBJ_NO EST_WAGES_AMT);
IF SUBSTR(PUT(EFF_DT_SEQ_NO,10.),1,4)>=2019 AND SUBSTR(PUT(EFF_DT_SEQ_NO,10.),1,4)<2026;
/*IF COMPRESS(POLICY_NO) IN ("HK22000049","0000033864","HK22011677");*/
RUN;

PROC SQL;
CREATE TABLE WC_&COUNTRY._WAGES_1 AS 
SELECT 
A.*,
B.REN_EFF_DT,
B.REN_EXP_DT,
CATX(A.POLICY_NO,YEAR(B.REN_EFF_DT),MONTH(B.REN_EFF_DT),DAY(B.REN_EFF_DT)) AS KEY_WAGES
FROM WC_WAGES_&COUNTRY. A
LEFT JOIN &COUNTRY._WC B ON (A.POLICY_NO=B.POLICY_NO AND A.RENL_CERT_NO=B.RENL_CERT_NO AND A.EFF_DT_SEQ_NO=B.EFF_DT_SEQ_NO /*AND A.LOCATION_NO=B.LOCATION_NO AND A.SECT_OBJ_NO=B.SECT_OBJ_NO AND A.RISK_OBJ_NO=B.RISK_OBJ_NO*/)
;
QUIT;

PROC SORT DATA=WC_&COUNTRY._WAGES_1 NODUP;
BY _ALL_ ;
RUN;

PROC SQL;
CREATE TABLE WC_&COUNTRY._WAGES_2 AS 
SELECT 
POLICY_NO,
REN_EFF_DT,
REN_EXP_DT,
RENL_CERT_NO,
EFF_DT_SEQ_NO,
LOCATION_NO,
SECT_OBJ_NO,
RISK_OBJ_NO,
KEY_WAGES,
SUM(EST_WAGES_AMT) AS WAGEROLL
FROM WC_&COUNTRY._WAGES_1
GROUP BY 1,2,3,4,5
;
QUIT;

PROC SORT DATA=WC_&COUNTRY._WAGES_2;
BY POLICY_NO REN_EFF_DT REN_EXP_DT KEY_WAGES;
RUN;

DATA WC_&COUNTRY._WAGES_3;
SET WC_&COUNTRY._WAGES_2;
BY POLICY_NO REN_EFF_DT REN_EXP_DT KEY_WAGES;
IF LAST.KEY_WAGES;
RUN;


PROC SQL;
CREATE TABLE &COUNTRY._WC1 AS
SELECT
A.POLICY_NO,
A.REN_EFF_DT,
A.REN_EXP_DT,
A.INSURED_NAME,
A.MAJOR_LINE_CD,
A.MINOR_LINE_CD,
A.CLASS_PERIL_CD,
A.MMCP,
A.COVERAGE_CD,
A.POL_SIGND_LINE_PCT,
/*A.PRDR_COMM_VALUE,*/
A.CURR_CODE_NM,
SUM(A.SCALC_PREM_AMT) AS GWP,
A.CLASS,
A.KEY_GROUP,
A.RECORD_TYPE_CD,
B.WAGEROLL
FROM &COUNTRY._WC A
LEFT JOIN WC_&COUNTRY._WAGES_3 B ON (A.POLICY_NO=B.POLICY_NO AND A.REN_EFF_DT=B.REN_EFF_DT AND A.REN_EXP_DT=B.REN_EXP_DT)
GROUP BY KEY_GROUP
ORDER BY KEY_GROUP
;
QUIT;

PROC SORT DATA=&COUNTRY._WC1;
BY POLICY_NO REN_EFF_DT KEY_GROUP;
RUN;

DATA &COUNTRY._WC_FINAL(DROP=KEY_GROUP);
SET &COUNTRY._WC1;
BY POLICY_NO REN_EFF_DT KEY_GROUP;
IF FIRST.KEY_GROUP;
RUN;

PROC DATASETS LIB=WORK NOLIST;
DELETE &COUNTRY._WC &COUNTRY._WC1 &COUNTRY._WC2 &COUNTRY._WC3
WC_WAGES_&COUNTRY.
WC_&COUNTRY._WAGES_1
WC_&COUNTRY._WAGES_2
WC_&COUNTRY._WAGES_3
;RUN;

DATA &COUNTRY._COMBINED_REN &COUNTRY._COMBINED_EXP;
SET &COUNTRY._PROPERTY_FINAL
&COUNTRY._LIABILITY_FINAL
&COUNTRY._WC_FINAL
;
KEY_JOIN_REN=COMPRESS(POLICY_NO||"-"||COVERAGE_CD||"-"||YEAR(REN_EFF_DT)||"-"||MONTH(REN_EFF_DT)||"-"||DAY(REN_EFF_DT));
KEY_JOIN_EXP=COMPRESS(POLICY_NO||"-"||COVERAGE_CD||"-"||YEAR(REN_EXP_DT)||"-"||MONTH(REN_EXP_DT)||"-"||DAY(REN_EXP_DT));
IF REN_EFF_DT>= &mydate. THEN OUTPUT &COUNTRY._COMBINED_REN;
IF REN_EFF_DT<&mydate. THEN OUTPUT &COUNTRY._COMBINED_EXP;
RUN;

PROC SQL;
CREATE TABLE &COUNTRY._POLICY_1 AS 
SELECT 
A.POLICY_NO AS REN_POLICY_NO,
A.REN_EFF_DT,
A.REN_EXP_DT,
A.INSURED_NAME,
A.MAJOR_LINE_CD,
A.MINOR_LINE_CD,
A.CLASS_PERIL_CD,
A.MMCP,
A.Class,
A.COVERAGE_CD AS REN_COVERAGE_CD,
A.POL_SIGND_LINE_PCT AS REN_AIG_SHARE_PC,
/*A.PRDR_COMM_VALUE AS REN_COMM_PC,*/
A.CURR_CODE_NM AS REN_CURRENCY_NM,
A.GWP AS REN_GWP_LOCAL_AM,
A.LIMIT AS REN_AIG_LIMIT_AM,
A.DEDUCTIBLE AS REN_DED_ATCH_AM,
A.WAGEROLL AS REN_WC_WAGEROLL,

B.POLICY_NO AS EXP_POLICY_NO,
B.COVERAGE_CD AS EXP_COVERAGE_CD,
B.REN_EFF_DT AS EXP_EFF_DT,
B.REN_EXP_DT AS EXP_EXP_DT,
B.POL_SIGND_LINE_PCT AS EXP_AIG_SHARE_PC,
/*B.PRDR_COMM_VALUE AS EXP_COMM_PC,*/
B.CURR_CODE_NM AS EXP_CURRENCY_NM,
B.GWP AS EXP_GWP_LOCAL_AM,
B.LIMIT AS EXP_AIG_LIMIT_AM,
B.DEDUCTIBLE AS EXP_DED_ATCH_AM,
B.WAGEROLL AS EXP_WC_WAGEROLL,
A.RECORD_TYPE_CD
FROM &COUNTRY._COMBINED_REN A
LEFT JOIN &COUNTRY._COMBINED_EXP B ON 
/*(A.POLICY_NO=B.POLICY_NO AND A.COVERAGE_CD=B.COVERAGE_CD AND A.Class=B.Class AND ABS(A.REN_EFF_DT-B.REN_EXP_DT)<=31)*/
(A.POLICY_NO=B.POLICY_NO AND A.Class=B.Class AND ABS(A.REN_EFF_DT-B.REN_EXP_DT)<=31)
;
QUIT;

DATA DATA.&COUNTRY._POLICY_&MONTH.(DROP=RECORD_TYPE_CD);
SET &COUNTRY._POLICY_1;
FORMAT NEW_RENEW_IN $CHAR20.;
IF EXP_EFF_DT=. THEN NEW_RENEW_IN="New Business"; Else NEW_RENEW_IN="Renewal";
FORMAT BRANCH_NM $CHAR30.;
BRANCH_NM=&CTR.;
IF COMPRESS(UPCASE(RECORD_TYPE_CD))='X' THEN POL_LIVE_STATUS="Canceled"; ELSE POL_LIVE_STATUS="Live";
RUN;


PROC DATASETS LIB=WORK NOLIST;
DELETE &COUNTRY._POLICY_1 &COUNTRY._COMBINED_EXP &COUNTRY._COMBINED_REN;RUN;

%MEND;

%DATAPULL(HK,"Hong Kong");
%DATAPULL(ID,"Indoneshia");
%DATAPULL(ML,"Malaysia");
%DATAPULL(SG,"Singapore");
%DATAPULL(TH,"Thailand");
Editor is loading...
Leave a Comment