Good morning,
Is there any way to tell proc sql not to sort my join data?
I'm trying to left join three tables that have already been sorted, and whenever I try to run the code it'd say sort execution failed because the utilloc lib (which is for sorting purposes) capacity is full. Is there any way to work around that or the join data will get re-sorted every time I do join? I'm using proc sql for joins because I need some aggregated calculations.
Hope my question makes sense.
@lilianlai wrote:
Good morning,
Is there any way to tell proc sql not to sort my join data?
Not at this time with the options I am aware of.
You might show your code you are using. Different approaches to coding, especially with Proc Sql, can create very different resource use. Someone may be able to provide help that reduces the memory use.
And it may be faster or less resource intense to use a procedure other than Proc Sql to do aggregates, such as Proc Summary, and join those results instead of doing it all in one Sql call.
PROC SQL;
CREATE TABLE lib.a AS
SELECT a.team,
a.id,
COUNT(DISTINCT(a.id)) AS Num_Pt,
/*Patients*/
COUNT(DISTINCT(case when a.note not is missing then a.id else 0 end)) AS RCPRL_Num_Pt, /*???*/
b.Rfrl_TotalNumPt,
d.totalvisit/calculated Num_pt as Avg_visit_pp format=10.2,
d.totaltxn/Calculated Num_pt as Avg_txn_pp format=10.2,
d.TotalPymt/Calculated Num_pt as Avg_Cost_pp format=10.2,
sum(c.pt_age)/Calculated Num_pt as Avg_Pt_Age format=8.2,
sum(c.Pt_Num_Days)/Calculated Num_pt as Avg_Num_SE_Days_pp format=8.2,
d.totalpymt/count(distinct a.txn_date) as Avg_Cost_pd format=8.2, /*???*/
sum(input(a.unit, 8.))/Calculated Num_pt as Avg_Num_unit_pp format=8.2,
d.totalpymt/sum(input(a.unit, 8.)) as Avg_Cost_penc format=8.2, /*???*/
b.cust_0_14,
b.cust_15_24,
b.cust_25_44,
b.cust_45_64,
b.cust_65plus
FROM lib.a as a
left join lib.b as b on (a.id = b.id)
left join lib.c as c on (a.id = c.id)
left join lib.d as d on (a.id = d.id)
GROUP BY a.team b.id;
QUIT;
This is the code. All data sets have been sorted... but it'll still say sort execution failed.
PROC SQL;
CREATE TABLE lib.a AS
SELECT a.team,
a.id,
COUNT(DISTINCT(a.id)) AS Num_Pt,
/*Patients*/
COUNT(DISTINCT(case when a.note not is missing then a.id else 0 end)) AS RCPRL_Num_Pt, /*???*/
b.Rfrl_TotalNumPt,
d.totalvisit/calculated Num_pt as Avg_visit_pp format=10.2,
d.totaltxn/Calculated Num_pt as Avg_txn_pp format=10.2,
d.TotalPymt/Calculated Num_pt as Avg_Cost_pp format=10.2,
sum(c.pt_age)/Calculated Num_pt as Avg_Pt_Age format=8.2,
sum(c.Pt_Num_Days)/Calculated Num_pt as Avg_Num_SE_Days_pp format=8.2,
d.totalpymt/count(distinct a.txn_date) as Avg_Cost_pd format=8.2, /*???*/
sum(input(a.unit, 8.))/Calculated Num_pt as Avg_Num_unit_pp format=8.2,
d.totalpymt/sum(input(a.unit, 8.)) as Avg_Cost_penc format=8.2, /*???*/
b.cust_0_14,
b.cust_15_24,
b.cust_25_44,
b.cust_45_64,
b.cust_65plus
FROM lib.a as a
left join lib.b as b on (a.id = b.id)
left join lib.c as c on (a.id = c.id)
left join lib.d as d on (a.id = d.id)
GROUP BY a.team b.id;
QUIT;
This is the code. All data sets have been sorted... but it'll still say sort execution failed.
@lilianlai wrote:
Good morning,
Is there any way to tell proc sql not to sort my join data?
I'm trying to left join three tables that have already been sorted, and whenever I try to run the code it'd say sort execution failed because the utilloc lib (which is for sorting purposes) capacity is full. Is there any way to work around that or the join data will get re-sorted every time I do join? I'm using proc sql for joins because I need some aggregated calculations.
Hope my question makes sense.
Sounds like your data is too large (or at least pushing the limits) of you infrastructure.
Explain more about why you feel you need to use SQL instead of using something like PROC SUMMARY to generate aggregate statistics.
If you do not need a cartesian join (no many-to-many relationships), a data step merge is the tool of choice.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.