Hi ,   I'm facing a problem while joining the two tables in the below fashion. not able to get the proper out put.   proc sql; create table Windscreen_RM as select t1.repyr,t1.repmthdt,sum(case when t1.flg_NilSettledInc="N" then t1.cnt_Settle end ) as Settled_with_pay label="Settled count with Pay", sum(case when t1.flg_NilSettledInc="Y" then t1.cnt_Settle end ) as Settled_wO_pay label="Settled count without Pay", sum(case when t1.rpnmth=. then t1.cnt_Outstand end) as outstand label="Outstanding Count",t2.Notified_cnt from WORK.RM_MOD_Windscreen t1 left outer join (select mdy(month(not_dat),01,year(not_dat)) as notif_mnth format=ddmmyy10.,count(distinct cats(clm_key,not_dat)) as Notified_cnt label="Reported Claims" from WORK.RM_MOD_Windscreen where notyr=repyr group by calculated notif_mnth) as t2 on t1.notyr=t1.repyr and t1.repmthdt=t2.notif_mnth group by t1.repyr,t1.repmthdt ; quit;   The individual outputs are below.   Individual out put of outer query   Individual output of inner query Reporting year Reporting month settled count with pay Nil settled out   Notif_mnth Notified_cnt 2013 01/01/2013 . . 2   01/01/2013 2 2013 01/02/2013 3 . 0   01/02/2013 1 2013 01/03/2013 . . 6   01/03/2013 7 2013 01/04/2013 2 . 4   01/06/2013 3 2013 01/05/2013 1 . 4   01/07/2013 1 2013 01/06/2013 1 . 5   01/12/2013 1 2013 01/07/2013 . . 6   01/05/2014 2 2013 01/08/2013 3 1 3   01/08/2014 1 2013 01/09/2013 . 1 2   01/02/2015 2 2013 01/10/2013 . . 2   01/04/2015 3 2013 01/11/2013 . . 2   01/06/2015 1 2013 01/12/2013 . . 3   01/09/2015 2 2014 01/05/2014 . . 1   01/11/2015 1 2014 01/06/2014 . . 1   01/02/2016 1 2014 01/07/2014 . . 1   01/03/2016 2 2014 01/08/2014 . . 1   01/04/2016 3 2014 01/09/2014 . . 1   01/07/2016 1 2014 01/10/2014 . . 1   01/11/2016 19 2014 01/11/2014 1 . 1   01/12/2016 22 2014 01/12/2014 . . 1   01/01/2017 26 2015 01/02/2015 . . 1   01/02/2017 23 2015 01/03/2015 . 1 0       2015 01/04/2015 1 . 0       2015 01/06/2015 1 . .       2015 01/07/2015 . . 1       2015 01/09/2015 . . 1       2015 01/10/2015 4 . 0       2015 01/11/2015 . . 1       2015 01/12/2015 . . 1       2016 01/02/2016 1 . 0       2016 01/03/2016 . . 1       2016 01/04/2016 2 1 1       2016 01/05/2016 . . 1       2016 01/06/2016 . . 1       2016 01/07/2016 1 . 1       2016 01/08/2016 . . 1       2016 01/09/2016 . . 1       2016 01/10/2016 1 . 1       2016 01/11/2016 6 . 13       2016 01/12/2016 7 . 31       2017 01/01/2017 6 . 19       2017 01/02/2017 19 1 30         Can someone tweak the code if i went somewhere wrong? ... View more

Hi ,   I'm trying the below program and need to show titles and footer in the exported excel file , may be like run date and some comments.   ods tagsets.csv file="%SYSFUNC(PATHNAME(WORK))/Motor_Daimler_&sysdate..csv" RS=NONE options(PREPEND_EQUALS="yes" QUOTE_BY_TYPE="yes" ); proc print data = Final_SUMMARY_12MTD label noobs; run; ods _all_ close;   Can someone amend the above program.   Thanks in advanceeee ... View more

Hi ,   Can some one help me how to get the functions for f(MTPL),f(MOD W/O Glass) , f( MOD W Glass) and f(Motor BI) in terms of AD,PI and TP components for the below conditions.   AD PI TP   MTPL MOD W/O Glass MOD W Glass Motor BI 0 0 0   1 0 0 0 0 0 1   1 0 0 0 0 1 0   0 0 0 1 0 1 1   1 0 0 1 1 0 0   0 1 1 0 1 0 1   1 1 1 0 1 1 0   0 1 1 1 1 1 1   1 1 1 1     Your help is much appreciated and if not atleast route me where can i get some help to get me problem resolved.   Regards, Reddi.   ... View more

Hi ,   i have the date in the below format  and need to get the group of ID which is having maximum count/observations( In the below example CML0005) . It's just an example , i've a millions of records and need to get the ID having maximum number of observations. data claims; input ID $ loss_code $; cards4; CML0001 27 CML0002 27C CML0002 27 CML0003 27Y CML0003 27C CML0003 27P CML0004 27O CML0004 27Y CML0004 27 CML0004 27P CML0005 27P CML0005 27O CML0005 27C CML0005 27 CML0006 27Y CML0006 27C CML0007 27O CML0007 27Y CML0008 27O CML0009 27P ;;;; run;   Many thnaks in advance...! ... View more