I got a code that uses a lot of left join with many tables. When I run this code, it takes more than an hour to run and at the end it gives error with Sort Execution Failure. So, I am thinking of breaking down that left join in multiple steps but I am not sure how to do it and need your help. The code is as: Proc sql;
create table newlib.Final_test as
SELECT
POpener.Name as Client,
Popener.PartyId as Account_Number,
Case
When BalLoc.ConvertedRefNo NE '' then BalLoc.ConvertedRefNo
else BalLoc.Ourreferencenum
End as LC_Number,
BalLoc.OurReferenceNum ,
BalLoc.CnvLiabilityCode as Liability_Code,
POfficer.PartyID as Officer_Num,
POfficer.Name as Officer_Name,
POpener.ExpenseCode,
BalLoc.IssueDate as Issue_Date format=mmddyy10.,
BalLoc.ExpirationDate AS Expiry format=mmddyy10.,
BalLoc.LiabilityAmountBase as Total_LC_Balance,
Case
When BalLoc.Syndicated = 0 Then BalLoc.LiabilityAmountBase
else 0
End as SunTrust_Non_Syndicated_Exposure,
Case
When BalLoc.Syndicated = 1 and BalLoc.PartOutGroupPkey NE 0 Then
BalLoc.LiabilityAmountBase
else 0
End as SunTrust_Syndicated_Exposure,
Case
When BalLoc.Syndicated = 1 and BalLoc.PartOutGroupPkey NE 0 Then
(BalLoc.LiabilityAmountBase - (BalLoc.LiabilityAmountBase *
(PParty.ParticipationPercent/100)))
Else BalLoc.LiabilityAmountBase
End as SunTrust_Exposure,
Case
When BalLoc.Syndicated = 1 and BalLoc.PartOutGroupPkey <> 0 Then
(BalLoc.LiabilityAmountBase * PParty.ParticipationPercent/100)
Else 0
End as Exposure_Held_By_Other_Banks,
PBene.Name as Beneficiary_Trustee,
cat(put(input(POpener.ObligorNumber,best10.),z10.),put(input
(BalLoc.CommitmentNumber,best10.),Z10.)) as Key,
case
when BalLoc.BeneCusip2 NE ' ' then catx
('|',Balloc.BeneCusip,Balloc.BeneCusip2)
else BalLoc.BeneCusip
End as Cusip,
Case
when balLoc.OKtoExpire = 1 then '0'
when balLOc.OKtoExpire=0 and BalLoc.AutoExtTermDays NE 0 then put
(Balloc.AutoExtTermDays,z3.)
when balLoc.OKtoExpire=0 and BalLoc.AutoExtTermsMonth NE 0 then put
(balloc.AutoExtTermsMonth,z3.)
else '000'
End as Evergreen
Case
when blf.AnnualRate NE 0 then put(blf.AnnualRate,z7.)
when blf.Amount NE 0 then cats('F',put(blf.amount,z7.))
else 'WAIVE'
End as Pricing,
FROM BalLocPrimary BalLoc
Left JOIN Party POpener on POpener.Pkey = BalLoc.OpenerPkey
Left join PartGroup PGroup on BallOC.PartOutGroupPkey = PGroup.pKey
Left join PartParties PParty ON PGroup.pKey = PParty.PartGroupPkey and
PParty.ParticipationPercent > 0 and
PParty.combined in
(select PPartParties.All_combined
from PPartParties /*group by PartGroupPkey, PartyPkey*/)
Left Join MemExpenseCodes ExpCodes on POpener.ExpenseCode = ExpCodes.Code
Left JOIN Party PBene on PBene.Pkey = BalLoc.BenePkey
Left join Party POfficer on POfficer.Pkey = BalLoc.AccountOfficerPkey
left join maxfee on maxfee.LocPrimaryPkey = BalLoc.LocPrimaryPkey
left join BalLocFee BLF on BLF.Pkey = maxfee.pkey
Where BalLoc.LetterType not in ('STBA','EXPA', 'FEE',' ') and
BalLoc.LiabilityAmountBase > 0 and BalLoc.irdb = 1
;
quit; Thank you, Shankar
... View more