%LET yr =2022 ;/* Current Calender year for which code needs to be run */
%LET mon =06 ;/* Current Calender month for which code needs to be run */
%LET posting_month =03 ;/*financial month for which report is generated.Ex - April -01*/
%Let posting_year = 2022 ;/*financial year of the month for which report is generated.
ex: 2016-2017 is written as 2016*/
%Let Treaty_begin_date = 20170515 ;/* First Treaty Begin date */
%Let Treaty_end_date = 20230331;/* Last treaty end date. Needs to be checked from RI Team every March */
%LET GCPP_RI_LIMIT = 29000000 ;/*(Before 31/03/2018)Check Maximum Reinsurance Limit with every new treaty*/
%LET GCPP_RI_LIMIT_rev = 50000000 ;/*(01/04/2018 onwards)Check Maximum Reinsurance Limit with every new treaty*/
%LET GCPP_Retention = 1000000 ; /*Before 31/03/2018*/
%LET GCPP_Retention_rev = 5000000 ; /*01/04/2018 onwards*/
/* Sum Insured */
/* Extracting general data from ACMV */
proc sql;
create table SAS0416acmv as
select A.RLDGACCT as Policy_no informat = $8. length = 8
,case
when A.GLSIGN eq '+' then A.ACCTAMT * -1 else A.ACCTAMT
end as given_Premium
,A.POSTYEAR
,A.POSTMONTH
,A.EFFDATE
,A.GLCODE
,substr(A.RLDGACCT,10,4) as Product_code
,BATCTRCDE
,case
when A.BATCTRCDE = 'T903' then 'New Buisness'
when A.BATCTRCDE = 'T928' then 'Renewal'
when A.BATCTRCDE = 'T926' then 'New Buisness Rollback'
when A.BATCTRCDE = 'T930' then 'Renewal Rollback'
when A.BATCTRCDE in ('T913','T934') then 'Cancellation'
when A.BATCTRCDE in ('T922') then 'Endorsements'
when A.BATCTRCDE in ('TRIT') then 'Reinstatement'
else 'Others'
end as Sub_buis_type
from SAS0416.ACMV_&yr&mon A
where A.GLCODE contains "RISK" /*Only these rows contain premium received rest all are not required*/
and A.POSTYEAR = "&posting_year"
and A.POSTMONTH = "&posting_month"
order by Policy_no;
quit;
/* Extracting data from ACMV for GCPP*/
proc sql;
create table acmv as
select Policy_no
,EFFDATE
,Product_code
,Sub_buis_type
,BATCTRCDE
,given_Premium
from SAS0416acmv
where Product_code in('8008',
'8004',
'8023',
'8036',
'8024',
'8051',
'8005',
'8006',
'8009',
'8030',
'8026',
'8035',
'8044',
'8052',
'8061',
'8066',
'8031',
'8079',
'8081',
'8086',
'8010',
'8032',
'8047',
'8064',
'8075',
'8080',
'8094',
'8302',
'8303',
'8320',
'8096',
'8001',
'8110',
'8093',
'8098',
'8100',
'8103',
'8104',
'8020',
'8082',
'8116',
'8205',
'8111',
'8215',
'9030',
'8040',
'8105',
'8106',
'8108',
'8118',
'8216',
'8220',
'8107',
'8217'
)/* Check new codes from IT/RI team every month */
order by Policy_no;
quit;
/* adding premiums of duplicate policies and removing duplicate Policy_no*/
proc sql;
create table acmv1 as
select Policy_no
,EFFDATE
,Product_code
,Sub_buis_type
,BATCTRCDE
,sum(given_Premium) as GWP
from acmv
group by Policy_no
order by Policy_no;
quit;
proc sort data=acmv1 nodupkey out=acmv2 dupout=acmv_duplicates ;
by Policy_no;
run ;
/* PSD,PED, SUBPKGID FROM GCHI */
proc sql ;
create table gchi as
select CHDRNUM as Policy_no
,CCDATE as Start_date
,CRDATE as End_date
,SUBPKGID as Subpackage_id
,DATIME
from SAS0416.GCHI_&yr&mon
ORDER BY Policy_no, Start_date, DATIME DESC;
quit;
proc sort data=gchi nodup dupout=gchi_duplicates;
by Policy_no;
run;
/* removing extensions(tenure < 32 days) from gchi */
data gchi;
set gchi;
Start = Start_date;
En = End_date;
Start_date1 = min(Start_date,20301231);
Start_date2 = put(Start_date1,8.);
Start_date = input(Start_date2,YYMMDD10.);
DROP Start_date1 Start_date2;
End_date1 = min(End_date,20301231);
End_date2 = put(End_date1,8.);
End_date = input(End_date2,YYMMDD10.);
DROP End_date1 End_date2 ;
Tenure = (End_date - Start_date)+1 ;
IF Tenure < 32 THEN DELETE ;
run;
DATA GCHI ;
SET GCHI ;
RENAME Start = Start_date En = End_date ;
DROP Start_date End_date ;
RUN ;
proc sort data=gchi nodup dupout=gchi_Duplicates ;
by Policy_no ;
run ;
/* merging gchi with acmv on basis of effective date of policy */
PROC SQL ;
Create table mergee as
select A.Policy_no
,A.Product_code
,A.EFFDATE
,B.Start_date
,B.End_date
,B.Tenure
,A.BATCTRCDE
,A.Sub_buis_type
,A.GWP
,B.subpackage_id
FROM acmv2 A
LEFT JOIN GCHI B
ON A.Policy_no = B.Policy_no AND A.EFFDATE BETWEEN B.Start_date AND B.End_date
ORDER BY A.Policy_no ;
QUIT ;
DATA MERGEE ; /* Incorrect policy issued then cancelled.Wrong End date *//*Not Required */
SET MERGEE ;
IF POLICY_NO = '10750280' THEN do ;
Start_date = 20160809 ; End_date = 20160808 ;
end ;
RUN;
/* policy cannot have missing Start date and End date */
DATA MERGES Merge_miss;
SET MERGEE ;
if missing(Start_date) or missing(End_date) then output Merge_miss ;
ELSE OUTPUT MERGES ;
RUN ;
Proc sort data = merges nodup dupout = merges_dupli;
by Policy_no ;
/*SI from GXHI */
proc sql ;
create table gxhi as
select CHDRNUM as Policy_no
,SUMINSU
,EFFDATE
,PRODTYP
from SAS0416.GXHI_&yr&mon
order by Policy_no, PRODTYP ,EFFDATE desc, DATIME desc ,DTETRM desc;
proc sort data=gxhi nodup;
by policy_no ;
quit ;
/*Latest SI from gxhi */
DATA gxhi ;
set gxhi ;
by Policy_no PRODTYP;
IF first.Policy_no and first.PRODTYP then output ;
run ;
/* merging SI */
proc sql ;
create table merges_gxhi as
select A.*, B.SUMINSU
from merges A
left join gxhi B
on A.Policy_no = B.Policy_no /*and A.Product_code=B.PRODTYP*/
order by policy_no ;
/*removing duplicate policies by adding their premiums*/
proc sql;
create table merges_gxhii as
select Policy_no
,EFFDATE
,Product_code
,Sub_buis_type
,BATCTRCDE
,Subpackage_id
,Start_date
,End_date
,SUMINSU
,Tenure
,sum(gwp) as Gross_Written_Premium
from merges_gxhi
group by Policy_no
order by Policy_no;
quit;
proc sort data=merges_gxhii nodup dupout=mg ;
by policy_no ;
run ;
/* SI should not be missing */
DATA merges_gxhii merges_gxhi_miss;
SET merges_gxhii ;
if missing(SUMINSU) then output merges_gxhi_miss ;
ELSE OUTPUT merges_gxhii ;
RUN ;
/*policy should lie in treaty period */
data merges_gxhii ;
set merges_gxhii ;
if Start_date >= "&Treaty_begin_date" and Start_date <= "&Treaty_end_date" then output ;
run ;
/*Age*/
/* member level data from GMHD */
Proc sql ;
create table gmhd as
select distinct CHDRNUM as Policy_no
,CLNTNUM
,DPNTTYP
,DTETRM
from SAS0416.GMHD_&yr&mon
ORDER BY Policy_no ;
proc sort data=gmhd;
by CLNTNUM ;
quit ;
Proc sql ;
create table CLNTPF_&yr&mon as
select distinct CLNTNUM AS Client_no
,CLTDOB as Client_dob
from SAS0416.CLNTPF_&yr&mon
where VALIDFLAG = '1' /* Current DOB of member is determined from this filter */
order by Client_no ;
Proc sort data = CLNTPF_&yr&mon nodup dupout=dup ;
by Client_no ;
/* merging dob with all members */
Proc sql ;
create table gmhd_clntpf as
select A.Policy_no
,A.CLNTNUM
,A.DPNTTYP
,A.DTETRM
,B.Client_dob
FROM gmhd A
LEFT JOIN CLNTPF_&yr&mon B
ON A.CLNTNUM = B.Client_no;
Proc sort data= gmhd_clntpf nodup dupout=dupli;
by Policy_no ;
run ;
/*individual and floater tag in policy*/
proc sql ;
CREATE TABLE glhd as
select CHDRNUM as Policy_no
,LMTWHOM as Policy_Type
,datime
,DTETRM
from SAS0416.GLHD_&yr&mon
WHERE LMTWHOM IN ('IF','IN')
ORDER BY Policy_no , datime ,DTETRM;
quit;
DATA glhd ;
set glhd ;
by Policy_no ;
if last.Policy_no then output ;/* latest tag of policy is pulled*/
run ;
proc sort data=glhd nodupkey dupout=d ;
by Policy_no ;
Proc sql ;
create table gm_cl_gl as
select A.Policy_no
,A.CLNTNUM
,A.DPNTTYP
,A.DTETRM
,A.Client_dob
,B.Policy_Type
FROM gmhd_clntpf A
LEFT JOIN glhd B
ON A.Policy_no = B.Policy_no
order by Policy_no;
/* Determining proposer and its coverage in policy */
PROC SQL ;
CREATE TABLE GCHD AS
SELECT distinct CHDRNUM as Policy_no
,COWNNUM as Proposer_no
,ZOWNERCV as Owner_covered
FROM SAS0416.GCHD_&yr&mon
order by Policy_no ;
proc sql ;
create table gm_cl_gl_gc as
select A.Policy_no
,A.CLNTNUM
,A.DPNTTYP
,A.DTETRM
,A.Client_dob
,A.Policy_Type
,B.Proposer_no
,B.Owner_covered
FROM gm_cl_gl A
LEFT JOIN GCHD B
ON A.Policy_no = B.Policy_no ;
quit ;
/* Deletion of proposer if not covered in policy*/
data gm_cl_gl_gc1 ;
set gm_cl_gl_gc ;
if CLNTNUM = Proposer_no and Owner_covered = 'N' then delete ;
run ;
PROC SORT DATA=gm_cl_gl_gc1 NODUP DUPOUT=X;
BY Policy_no ;
quit ;
/*Merging member level info with the Req policies */
Proc sql ;
create table Age_base as
select B.Policy_no
,A.CLNTNUM
,A.DPNTTYP
,A.DTETRM
,A.Client_dob
,A.Policy_Type
,A.Proposer_no
,B.Start_date
,B.BATCTRCDE
,B.Gross_Written_Premium
FROM merges_gxhii B
LEFT JOIN gm_cl_gl_gc1 A
ON A.Policy_no = B.Policy_no
order by B.policy_no ,A.CLNTNUM ,A.DTETRM desc ;
quit;
/* Deletion of Termed Members */
Data Age_base1 ;
set Age_base ;
Start = Start_date ;
Term = DTETRM ;
Start_date1 = min(Start_date,20301231);
Start_date2 = put(Start_date1,8.);
Start_date = input(Start_date2,YYMMDD10.);
DTETRM1 = min(DTETRM,20301231);
DTETRM2 = put(DTETRM1,8.);
DTETRM = input(DTETRM2,YYMMDD10.);
if DTETRM < Start_date then delete ;
drop Start_date1 Start_date2 start_date DTETRM1 DTETRM2 DTETRM ;
rename Start = Start_date Term = DTETRM ;
run ;
Proc sort data = Age_base1 nodupkey dupout=dup ;
by Policy_no /*descending clntnum*/ ;
quit ;
/* Determining age as on last birthday */
data Age ;
SET Age_base1 ;
Client_birth_dt = Client_dob ;
Policy_start_dt = Start_date ;
Client_dob1=min(Client_dob,20301231);
Client_dob2=put(Client_dob1,8.);
Client_dob=input(Client_dob2,yymmdd10.);
DROP Client_dob1 Client_dob2;
Start_date1=min(Start_date,20301231);
Start_date2=put(Start_date1,8.);
Start_date=input(Start_date2,yymmdd10.);
DROP Start_date1 Start_date2;
Age = floor((Start_date - Client_dob)/365.25);
drop Client_dob Start_date;
run ;
Proc sort data=Age nodup out=correct dupout=kk;
by policy_no descending Age ;
quit;
/* For floater policy Member with max age is taken and
For individual all the members will be considered in RI Premium calculation */
data max_age ;
set correct ;
by policy_no ;
if Policy_Type in ('IF' ) then do ;
if first.policy_no then output ;
end ;
IF Policy_Type in ('IN') THEN OUTPUT ;
drop DTETRM ;
run ;
Proc sort data=max_age ;
by policy_no ;
Proc sort data=merges_gxhii ;
by policy_no ;
run ;
/*Merging age and SI */
data age_SI AGE SI nowhere;
merge merges_gxhii (in=a)
max_age (in=b);
by Policy_no ;
if A=1 and B=1 then output age_SI ;
else if A=0 and B=0 then output nowhere ;
else if A=1 and B=0 then output SI ;
else if A=0 and B=1 then output AGE ;
run ;
data reinsurer_share ;
set age_SI ;
quota = 0.05 * SUMINSU ;/* Fixed 5% */
balance = SUMINSU - quota ;
if START_DATE <= 20180331 then Retention = &GCPP_Retention; /* Check retention of Religare and change accordingly */
else Retention = &GCPP_Retention_rev;
if START_DATE <= 20180331 then Max_Limit = &GCPP_RI_LIMIT; /*Check Max limit of Sum Insured and change accordingly */
else Max_Limit = &GCPP_RI_LIMIT_rev;
if SUMINSU > MAX_LIMIT then LIMIT = 'limit exceeds';
If balance < Retention then RI_Share = 0; /* reinsurer share */
else RI_Share = balance - Retention ;
tenure_yr = round(tenure/365) ;
run ;
