DATA Step, Macro, Functions and more

Breaking down of multiple left joins into different steps in Proc Sql

Reply
Occasional Contributor
Posts: 6

Breaking down of multiple left joins into different steps in Proc Sql

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

Super User
Posts: 5,431

Re: Breaking down of multiple left joins into different steps in Proc Sql

Splitting up this to several steps is - just work.
Someone thst was able to create this vast SQL step you should be able to split it up.
Data never sleeps
Occasional Contributor
Posts: 6

Re: Breaking down of multiple left joins into different steps in Proc Sql

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.

Super User
Posts: 3,256

Re: Breaking down of multiple left joins into different steps in Proc Sql

Post the log of your split queries.

Super User
Posts: 5,431

Re: Breaking down of multiple left joins into different steps in Proc Sql

http://support.sas.com/kb/39/705.html

If a join with only two tables fail, there are few other options than getting more resources. Be sure to carry only the necessary columns through each join, an shorten long char variables if possible.
As a last resort pre sort the data using PROC SORT TAGSORT and then use DATA step with MERGE.
Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 123 views
  • 0 likes
  • 3 in conversation