topic Re: Tell SAS not to sort my data in New SAS User

Tell SAS not to sort my data

lilianlai — Fri, 20 Sep 2019 16:16:39 GMT

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.

Re: Tell SAS not to sort my data

ballardw — Fri, 20 Sep 2019 16:41:18 GMT

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

Re: Tell SAS not to sort my data

JosvanderVelden — Fri, 20 Sep 2019 16:48:31 GMT

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.

Re: Tell SAS not to sort my data

lilianlai — Fri, 20 Sep 2019 16:54:39 GMT

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.

Re: Tell SAS not to sort my data

lilianlai — Fri, 20 Sep 2019 16:55:48 GMT

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.

Re: Tell SAS not to sort my data

Tom — Fri, 20 Sep 2019 17:54:35 GMT

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

Re: Tell SAS not to sort my data

Kurt_Bremser — Fri, 20 Sep 2019 17:59:16 GMT

If you do not need a cartesian join (no many-to-many relationships), a data step merge is the tool of choice.