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

st_012 — Fri, 07 Jul 2017 14:53:04 GMT

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

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

LinusH — Fri, 07 Jul 2017 19:29:50 GMT

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.

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

st_012 — Fri, 07 Jul 2017 19:34:42 GMT

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.

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

LinusH — Sat, 08 Jul 2017 03:47:03 GMT

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.

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

SASKiwi — Sat, 08 Jul 2017 22:20:03 GMT

Post the log of your split queries.

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

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

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

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

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

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