Dear sirs, I have a data set from where i need to generate rolling 12 months sum on all the numeric variables. I'm not able to provide the sample data for you but the variable names are below. Variable Name repyr repqtr repmthdt DSN hbr_pi hbr_ser htm_pi htm_ser flg_NilSettledPay flg_Windscreen ap_ind type inc_amtt_bnd prefix2 product_desc Source_System cnt_Outstand_Sum pay_amtm_Sum pay_amtt_Sum rt_est_amt_Sum inc_outstand_Sum rec_settle_Sum inc_settle_Sum life_cyc_Sum cnt_notified_Sum cnt_Settle_Sum cnt_Reopen_Sum rec_amtm_Sum inc_amtt_Sum notificationtime_Sum nillsettled_Sum Type Date Date Date Character Character Character Character Character Character Character Character Character Character Character Character Character Numeric Numeric Numeric Numeric Numeric Numeric Numeric Numeric Numeric Numeric Numeric Numeric Numeric Numeric Numeric and my program code is below using proc sql. proc sql nowarn; create table summary_12MTD as select distinct t1.repyr label="Reporting Year",t1.repqtr label="Reporting Quarter",t1.repmthdt label="Reporting Month",t1.DSN label="Dataset Name", t1.hbr_pi label="PI Handling Branch",t1.hbr_ser label="Service Handling Branch",t1.htm_pi label="PI Handling Team", t1.htm_ser label="Service Handling Team",t1.flg_NilSettledPay label="Nill Settled Flag", t1.flg_Windscreen label="Windscreen Flag",t1.ap_ind label="PI Indicator",t1.type label="Claim Type", t1.inc_amtt_bnd label="Incurred Amount Band",t1.prefix2 label="Policy Prefix", t1.product_desc label="Product Group",t1.Source_System,t1.cnt_Outstand_sum label="Monthly Outstanding Count", t1.pay_amtm_sum label="Monthly Paid Movement",t1.pay_amtt_sum label="Monthly Total Paid",t1.rt_est_amt_sum label="Estimate Amount", t1.inc_outstand_sum label="Outstanding Incurred Amount",t1.rec_settle_sum label="Settled Recovered Amount(Monthly)", t1.inc_settle_sum label="Settled Incurred Amount(Monthly)",t1.life_cyc_sum label="Life cycle(Monthly)", t1.cnt_notified_sum label="Notified Count(Monthly)",t1.cnt_Settle_sum label="Settled Count(Monthly)", t1.cnt_Reopen_sum label="Reopen Count(Monthly)",t1.rec_amtm_sum label="Recovered Movement(Monthly)", t1.inc_amtt_sum label="Total Incurred Amount(Monthly)",t1.notificationtime_sum label="Notification days(Monthly)", t1.nillsettled_sum label="Nill Settled Count(Monthly)", sum(t2.life_cyc_sum) as life_cyc_12MTD label="12MTD Lifecycle", sum(t2.pay_amtt_sum) as pay_amtt_12MTD label="12MTD Total Paid", sum(t2.rec_settle_sum) as rec_settle_12MTD label="12MTD Settled Recovered Amount", sum(t2.inc_settle_sum) as inc_settle_121MTD label="12MTD Settled Incurred Amount", sum(t2.cnt_notified_sum) as cnt_notified_12MTD label="12MTD Notified Count", sum(t2.cnt_Settle_sum) as cnt_Settle_12MTD label="12MTD Settled Count", sum(t2.cnt_Reopen_sum) as cnt_Reopen_12MTD label="12MTD Reopened Count", sum(t2.inc_amtt_sum) as inc_amtt_12MTD label="12MTD Incurred Total", sum(t2.notificationtime_sum) as notificationtime_12MTD label="12MTD Notification Time", sum(t2.nillsettled_sum) as nillsettled_12MTD label="12MTD Nill Settled Count" from work.summary t1 left join work.summary t2 on t1.DSN =t2.DSN and t1.hbr_pi =t2.hbr_pi and t1.hbr_ser =t2.hbr_ser and t1.htm_pi =t2.htm_pi and t1.htm_ser =t2.htm_ser and t1.flg_NilSettledPay =t2.flg_NilSettledPay and t1.flg_Windscreen =t2.flg_Windscreen and t1.ap_ind =t2.ap_ind and t1.type =t2.type and t1.inc_amtt_bnd =t2.inc_amtt_bnd and t1.prefix2 =t2.prefix2 and t1.product_desc =t2.product_desc and t1.Source_System =t2.Source_System and intck("month",t1.repmthdt,t2.repmthdt) between 0 and -11 group by t1.repyr,t1.repqtr,t1.repmthdt,t1.DSN,t1.hbr_pi,t1.hbr_ser,t1.htm_pi,t1.htm_ser,t1.flg_NilSettledPay, t1.flg_Windscreen,t1.ap_ind,t1.type,t1.inc_amtt_bnd,t1.prefix2,t1.product_desc,t1.Source_System, t1.nillsettled_Sum,t1.notificationtime_Sum,t1.inc_amtt_Sum,t1.rec_amtm_Sum,t1.cnt_Reopen_Sum, t1.cnt_Settle_Sum,t1.cnt_notified_Sum,t1.life_cyc_Sum,t1.inc_settle_Sum,t1.rec_settle_Sum, t1.inc_outstand_Sum,t1.rt_est_amt_Sum,t1.pay_amtt_Sum,t1.pay_amtm_Sum,t1.cnt_Outstand_Sum ; quit; I'm not able to get the correct 12MTD values... ANY HELP IS MUCH APPRECIATED.
... View more
