BookmarkSubscribeRSS Feed
lilianlai
Calcite | Level 5

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. 

6 REPLIES 6
ballardw
Super User

@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.

lilianlai
Calcite | Level 5
 

 

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
Calcite | Level 5
 

 

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.

JosvanderVelden
SAS Super FREQ
Have a look at the information here: http://support.sas.com/kb/39/705.html

Consider removing the DISTINCT keyword and the ORDER BY from the query to prevent additional sorts, or run a subsequent query to pull the distinct rows and order the data.
Tom
Super User Tom
Super User

@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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 6 replies
  • 805 views
  • 0 likes
  • 5 in conversation