Sort Initialization Failure - PROC SQL in SAS EG

[sasspan]

Hello - 

I have a 2400 line program within SAS EG program.  The code runs until the very last step where I connect 38 tables through left outer joins.  I had some issues with the joins at first, but they have been corrected.  The only error I am getting now is "Sort Initialization Failure."  I have tried setting the SORTSIZE option to 0, and I also tried the MEMSIZE but can't get that one to work.  It throws a warning for MEMSIZE only works at the beginning of a session.  I tried to log out and log back in and run the MEMSIZE first, and still get the same error.  Any thoughts on the sort initialization failure error would be great!

Andrew

[ballardw]

Is that 38 joins in a single Proc Sql call? Are all of the joins on the same variable or do you have different variables on different joins?

There is a "hidden" sort involved with the joins and if you have different variables then the internal attempts to maintain different orders on one of the sets might be an issue.

If the joins are all on the same variables it may be worth sorting all of them first (or explicitly order them in previous steps) to reduce overhead.

Or reduce the scale by only doing a few joins per Proc SQL call.

You might post the code of that join so we can make specific suggestions.

[sasspan]

I posted a "quick reply" but it isn't showing up on my computer, so I'm posting a regular reply...sorry if I am being redundant.  I don't think it has to do with ordering the variables in previous steps as SQL does this for you, unlike SAS.  They are all the same variable, just different tables.  I may try your approach of breaking it up into smaller proc sqls, I like that.  Here's the code for review - 

proc sql;
create table pmsi_tst.phase_1_variables_final as
select
a.cl_claim_number,
b.tramadol_400,
c.opioid_benz_sed_stim,
d.opioid_benz,
e.opioid_carisoprodol,
f.opioid_benz_smr,
g.stim_sed,
h.apap_4k,
i.triazolam_LT,
j.tempazepam_LT,
k.rozerem_LT,
l.quazepam_LT,
m.flurazepam_LT,
n.eszopiclone_LT,
o.estazolam_LT,
p.zaleplon_LT,
q.new_york_brand,
r.narc_20_days,
s.polypharmacy,
t.chronic_cyclo,
u.chronic_zolpidem,
v.narc_42_days,
w.narc_60_days,
x.opioid_60_doi,
y.narc_30_days,
z.smr_6_weeks,
aa.benzo_30_days,
bb.opioid_3_plus_last_30,
cc.chronic_narc,
dd.first_narc_trigger,
ee.chronic_smr,
gg.first_narc_10_ds_trig,
ii.new_long_narc,
jj.mult_narc_docs,
jj.mult_pharm,
jj.mult_docs,
jj.narc_3_or_more,
jj.dupe_smr,
jj.dupe_nsaid,
jj.dupe_sed,
jj.drug_classes_5_plus,
rr.cl_55_over_1000_billed,
rr.spend_1500_10k,
rr.spend_1k_1500,
rr.spend_500_1k,
a.age_58_60,
a.age_60_plus,
yy.med_0_50,
yy.med_50_100,
yy.med_100_150,
yy.med_over_150,
a.anti_narcoleptic,
a.anti_retroviral,
a.carisoprodol,
a.narcan,
a.meperidine,
a.opemprazole,
a.terocin,
a.testosterone,
a.phys_dispense,
a.anticonvulsant,
a.antidepressant,
a.anti_inflam,
a.antipsych,
a.compound,
a.non_wc_drug,
a.narc_nonnarc_pain,
a.opioid,
a.steroid,
a.ulcer,
a.fentanyl,
a.duexis,
a.vemovo,
a.lyrica,
a.sed_hyp,
a.percocet,
a.med5k,
xx.celebrex_200,
zz.new_LA_tramadol_trig
from solo_triggers a
left join tramadol_over_400 b on a.cl_claim_number = b.cl_claim_number
left join opioid_benz_sed_stim c on b.cl_claim_number = c.cl_claim_number
left join opioid_benz d on c.cl_claim_number = d.cl_claim_number
left join opioid_carisoprodol e on d.cl_claim_number = e.cl_claim_number
left join opioid_benz_smr f on e.cl_claim_number = f.cl_claim_number
left join stim_sed g on f.cl_claim_number = g.cl_claim_number
left join apap_over_4k h on g.cl_claim_number = h.cl_claim_number
left join triazolam_final i on h.cl_claim_number = i.cl_claim_number
left join tempazepam_final j on i.cl_claim_number = j.cl_claim_number
left join rozerem_final k on j.cl_claim_number = k.cl_claim_number
left join quazepam_final l on k.cl_claim_number = l.cl_claim_number
left join flurazepam_final m on l.cl_claim_number = m.cl_claim_number
left join eszopiclone_final n on m.cl_claim_number = n.cl_claim_number
left join estazolam_final o on n.cl_claim_number = o.cl_claim_number
left join zaleplon_final p on o.cl_claim_number = p.cl_claim_number
left join new_york_brand_final q on p.cl_claim_number = q.cl_claim_number
left join narc_20_days r on q.cl_claim_number = r.cl_claim_number
left join poly_7 s on r.cl_claim_number = s.cl_claim_number
left join chronic_cyclo t on s.cl_claim_number = t.cl_claim_number
left join chronic_zolpidem u on t.cl_claim_number = u.cl_claim_number
left join narc_42_days v on u.cl_claim_number = v.cl_claim_number
left join narc_60_days w on v.cl_claim_number = w.cl_claim_number
left join opioid_60_doi x on w.cl_claim_number = x.cl_claim_number
left join narc_30_days y on x.cl_claim_number = y.cl_claim_number
left join smr_6_weeks z on y.cl_claim_number = z.cl_claim_number
left join benzo_30_days aa on z.cl_claim_number = aa.cl_claim_number
left join opioids_3_30 bb on aa.cl_claim_number = bb.cl_claim_number
left join chronic_narc cc on bb.cl_claim_number = cc.cl_claim_number
left join first_second_opioid_trig dd on cc.cl_claim_number = dd.cl_claim_number
left join chronic_smr ee on dd.cl_claim_number = ee.cl_claim_number
left join first_narc_10_trigger gg on ee.cl_claim_number = gg.cl_claim_number
left join new_la_opioid_trig ii on gg.cl_claim_number = ii.cl_claim_number
left join multiple_occur_behavior jj on ii.cl_claim_number = jj.cl_claim_number
left join spend_variables rr on jj.cl_claim_number = rr.cl_claim_number
left join med_all_claims yy on rr.cl_claim_number = yy.cl_claim_number
left join celebrex_over_200 xx on yy.cl_claim_number = xx.cl_claim_number
left join new_la_tramadol_trig zz on xx.cl_claim_number = zz.cl_claim_number;
quit;

[sasspan]

My replies are not showing up for some reason, I'm thinking it's because of the code being in it, so I'll just add an attachment and see if that works.  I don't think it's the ordering of the variables. SQL does this for you.  I will try breaking it into diff proc sqls and see if that works.  For now, here's the code for your review. 

[sasspan · Accepted solution]

The issue has been solved.  I did not have all of my left joins on a single driving table as I was experimenting with different ways to bring the data together.  Now that all left joins reference the same driver table, it works. 

[TomKari]

Bummer!

I'm going out on a limb here, as I have no way to verify anything, but I'm pretty sure that SORT routines will use disk overflow if memory fills up. I suggest you check the disk area being used for work and utilities, and make sure there's lots of space available.

Tom

[Kurt_Bremser]

38 tables in one SQL? I'd call that at least a bit optimistic.

Split that into stages. Depending on the relationships, a series of sort/data steps might even perform better.