Hi, please can someone please help me. I have this code attached below. the outcome on the report needs to be as follow: NumApp =2166 NumGoedKeur =300 Numbook =274 I managed to get the Approved Applications that off 274, but the problem comes in where the NumApp and the NumGoedkeur is the same as 274, and it actually needs to be NumApp= 2166, and the NumGoedKeur is 300. so somewhere in my code i am not calculating the correct table in order to get the NumApps and the NumGoedkeur, and due to the NumApps, NumGoedkeur and Numbook is pulling through the same amount of 274, the calculations off all the other fields is showing 100% please can someone please help me just to sort out this small calculation mistake then i can create the rest of the report. Proc sql; create table data1 as select A.KeyDateEntr,a.Applm_StatusCode, a.ApplM_Status,a.ApplM_MarketCode,a.ApplM_MarketName, a.KeyAInfra, A.Applm_SCategory,a.Applm_HubName,a.KeyDataDate,a.Applm_TimeLaps, a.ApplM_Prindebt,A.Applm_NedChannel,a.ApplM_Source,a.Applm_SourceDesc, B.KeyDateBook,b.AccoM_RepStatus,b.AccoM_MotorDiv, b.AccoM_RateOffset, b.AccoM_RateType,b.MFCRef,b.AccoM_Rate, b.AccoM_Prindebt,b.monthbook from WerkWM.MartAppMini as a left join werkwm.martaccount as b on a.KeyIBIS = b.KeyIBIS where b.monthbook = 202308 and ApplM_Source in ('32', '33', '34'); quit; Data data2; set data1; if KeyDateEntr ne . then AppCount = 1; if ApplM_StatusCode = 'CAPD' then Goedkeur = 1; if KeyDatebook ne . then NumBook = 1; if AccoM_RateType = "PRM" then PRM = 1; else if AccoM_RateType = "FXD" then Fixed = 1; else if AccoM_RateType = "OFC" then OFC = 1; Attrib NEDChannelDesc format = $25. label = 'NED Channel Description'; NEDChannelDesc = put(ApplM_NedChannel,NEDChannelDesc.); run; proc sort data = data2; by KeyAInfra; run; data data3(drop = KeyAInfra); merge data2(in = hoof) WerkWM.DIMAPPLINFRA(keep = KeyApplInfraM ApplM_ExternalRef rename = (KeyApplInfraM = KeyAInfra)); by KeyAInfra; if hoof; if ApplM_Source = '32' then do; if missing(ApplM_ExternalRef) then NEDChannelDesc = catt(NEDChannelDesc,'(Online)'); else NEDChannelDesc = catt(NEDChannelDesc,'(Branch)'); end; run; Proc Sql; Create Table WerkHubApps as select Applm_SourceDesc,NEDChannelDesc, sum(AppCount) as NumApps , sum(Goedkeur) as NumGoedkeur, sum(case when ApplM_SCategory = "Risk1" then 1 else 0 end) as Risk1 format = 8., sum(case when ApplM_SCategory = "Risk2" then 1 else 0 end) as Risk2 format = 8., sum(case when ApplM_SCategory = "Risk3" then 1 else 0 end) as Risk3 format = 8., sum(case when ApplM_SCategory = "Risk4" then 1 else 0 end) as Risk4 format = 8. from data3 group by Applm_SourceDesc, NEDChannelDesc UNION ALL select "Total" as Applm_SourceDesc,"Total" as NEDChannelDesc, sum(AppCount) as NumApps , sum(Goedkeur) as NumGoedkeur, sum(case when ApplM_SCategory = "Risk1" then 1 else 0 end) as Risk1 format = 8., sum(case when ApplM_SCategory = "Risk2" then 1 else 0 end) as Risk2 format = 8., sum(case when ApplM_SCategory = "Risk3" then 1 else 0 end) as Risk3 format = 8., sum(case when ApplM_SCategory = "Risk4" then 1 else 0 end) as Risk4 format = 8. from data3; create table WerkHubBook as select Applm_SourceDesc,NEDChannelDesc, sum(NumBook) as NumBook, sum(AccoM_Prindebt) as AmtBook format = KRandm., sum(Fixed) as Fixed label = "# Fixed Rate", sum(PRM) as PRM label = "# Prime Link", sum(OFC) as OFC label = "# Staff Rate", sum(AccoM_RateOffset*AccoM_Prindebt) as AmtRatWei, sum(AccoM_Prindebt) as AmtBook1 format = 15.2, calculated AmtRatWei / calculated AmtBook as WAVG format = 6.2 from data3 where NumBook = 1 group by Applm_SourceDesc, NEDChannelDesc UNION ALL select "Total" as Applm_SourceDesc,"Total" as NEDChannelDesc, sum(NumBook) as NumBook, sum(AccoM_Prindebt) as AmtBook format = KRandm., sum(Fixed) as Fixed label = "# Fixed Rate", sum(PRM) as PRM label = "# Prime Link", sum(OFC) as OFC label = "# Staff Rate", sum(AccoM_RateOffset*AccoM_Prindebt) as AmtRatWei, sum(AccoM_Prindebt) as AmtBook1 format = 15.2, calculated AmtRatWei / calculated AmtBook as WAVG format = 6.2 from data3 where NumBook = 1; quit; /*Building the report*/ Data Report; merge werkhubApps WerkHubBook; Attrib GoedKeurPerc format = 4.1 Label = "Approval Ratio" BookPerc format = 4.1 Label = "Book Ratio" AmtBook format = KRandm.; by Applm_SourceDesc NEDChannelDesc; if NumApps > 0 then GoedKeurPerc = round(NumGoedkeur / NumApps * 100,0.1); else GoedKeurPerc = 0; if NumGoedkeur > 0 then BookPerc = round(NumBook / NumGoedkeur * 100,0.1); else BookPerc = 0; if missing(Fixed) then Fixed = 0; if missing(PRM) then PRM = 0; if missing(OFC) then OFC = 0; if missing(NEDChannelDesc) then delete; run; thank you
... View more
