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
I break it into pieces but still that error "SORT EXECUTION FAILURE" appears. I know that error is a memory issue.
I am asking this question if there is other way we can write without needing much memory.
Post the log of your split queries.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.