More in details, what I need to do is to run the SAS script today and five days ago and to check if the result of the SQL query gives the same results.
%MACRO count_classic_prop_edpplatform(CIE);
/********** UAT environment *****************/
LIBNAME CNTPDMU SASIOSNF
DATABASE=CONTRACTPL_UAT_DB_GLD
SERVER=
SCHEMA=CONTRACTPL_DM
AuthDomain="SNOWFLAKE_CONTRACTPL_DM_UAT";
run;
/****** The SQL Query ******************/
PROC SQL;
CREATE TABLE temp.inforce_prop_contact_&suffix. As
SELECT
contractVer.POL_NBR as policy,
contractVerOriginal.TRM_NBR,
contractVerOriginal.TRM_VER_NBR,
riskVer_RE.REF_NBR AS RSK_REF_NBR,
contractVer.CNTRCT_EFF_DT,
contractVer.CNTRCT_EXPRY_DT,
contractVerOriginal.TRX_EFF_DT,
contractVer.TRX_TYP_CD,
factPremium.OFFSET_IND,
factPremium.TRM_PREM_AMT as FTPREM,
contractVer.MAIN_INSRR_CO_NBR,
contractVer.CTRLG_PROV_ID as province,
contractVer.BRNCH_REF_NBR,
contractVer.DSTRBTR_NBR,
contractVer.ORIG_EFF_DT,
contractVer.PREFRD_CNTRCT_LOLTY_DT,
contractVer.MHA_ELGBLTY_IND,
contractVer.MRKT_TRTRY_REF_NBR,
roleVer_INSRD.PR_INSRR_CD,
contractVer.INTRO_CD ,
riskVer_RE.LOB_CD,
contractVer.CNTRCT_TYP_CD,
contractVerOriginal.AUTHRSTN_DT,
Temptbl.DIM_PL_CNTRCT_VER_KEY,
Temptbl.LKP_PROGRAM_KEY,
Temptbl.DIM_PL_ROLE_VER_INSRD_KEY,
Temptbl.PROG_NM_CD,
Temptbl.PROG_CAT_CD,
coalesce(Temptbl.Affinity_Ind,'N') as Affinity_Ind
FROM cntrctDM.FCT_PL_COV_DRVR_PREM factPremium
JOIN cntrctDM.DIM_PL_CNTRCT_VER contractVer
ON factPremium.DIM_PL_CNTRCT_VER_KEY = contractVer.DIM_PL_CNTRCT_VER_KEY
JOIN cntrctDM.DIM_PL_CNTRCT_VER contractVerOriginal
ON factPremium.ORGNL_DIM_PL_CNTRCT_VER_KEY = contractVerOriginal.DIM_PL_CNTRCT_VER_KEY
JOIN cntrctDM.DIM_PL_RSK_VER_RE riskVer_RE
ON factPremium.DIM_PL_RSK_VER_RE_KEY = riskVer_RE.DIM_PL_RSK_VER_RE_KEY
JOIN cntrctDM.DIM_PL_COV_VER covVer_PRMRY
ON factPremium.PRMRY_DIM_PL_COV_VER_KEY = covVer_PRMRY.DIM_PL_COV_VER_KEY
JOIN cntrctDM.DIM_PL_ROLE_VER_INSRD roleVer_INSRD
ON factPremium.DIM_PL_ROLE_VER_INSRD_KEY = roleVer_INSRD.DIM_PL_ROLE_VER_INSRD_KEY
LEFT JOIN Temptbl
ON (Temptbl.DIM_PL_CNTRCT_VER_KEY=contractVer.DIM_PL_CNTRCT_VER_KEY)
WHERE
contractVer.CNTRCT_TYP_CD not in ('4','5') and
factPremium.DIM_PL_RSK_VER_RE_KEY > -1 AND
/* Eliminating shell transactions */
contractVer.TRX_TYP_CD NE "SHL" AND
/* Selecting policies possibly in force based on CNTRCT_EFF_DT,CNTRCT_EXPRY_DT, and
(INTNX("MONTH", MDY(&mm., 1, &yyyy.), 1)-1)*/
contractVer.CNTRCT_EFF_DT <= (INTNX("MONTH", MDY(&mm., 1, &yyyy.), 1)-1) AND
contractVer.CNTRCT_EXPRY_DT > (INTNX("MONTH", MDY(&mm., 1, &yyyy.), 1)-1) AND
contractVerOriginal.TRX_EFF_DT <= (INTNX("MONTH", MDY(&mm., 1, &yyyy.), 1)-1) AND
/*contractVer.POL_NBR in (&prop_policy_list.) and*/
/*** risk when the policy is renewed that should not be considered inforce CHG74888 ****/
(Not(covVer_PRMRY.MODIF_CD = 'S' And factPremium.WPRM_AMT = 0) And
Not(contractVerOriginal.TRX_TYP_CD = 'RWL' And factPremium.OFFSET_IND='Y')
) AND
/* Filtering condition to obtain only the transactions from Belair */
%if &cie=be %then
%do;
contractVer.MAIN_INSRR_CO_NBR = "010"
%end;
/* Filtering conditions to obtain only the transactions from Intact Quebec */
%else %if &cie=gc %then
%do;
contractVer.MAIN_INSRR_CO_NBR = "001" and
contractVer.CTRLG_PROV_ID="QC"
%end;
ORDER BY
contractVer.MAIN_INSRR_CO_NBR, contractVer.CTRLG_PROV_ID, policy,
riskVer_RE.REF_NBR, contractVer.TRM_NBR, contractVer.TRM_VER_NBR, factPremium.OFFSET_IND DESC
;
QUIT;
%put ===================== End of count_classic_prop_edpplatform ==== Company =&cie. =================================================== ;
%exit:
%MEND count_classic_prop_edpplatform;
%count_classic_prop_edpplatform(gc);
by using the time travel on a Snow Flake database but in SAS
How do we do that ?
Provide an example because up to now we are using a libname to connect to the good data base not a pass through connection
... View more
