ERROR: Sort execution failure

[Ronein]

Hello

I get this error

NOTE: SAS threaded sort was used.
ERROR: Sort execution failure.

and run this code

proc sql;
create table ABT2_ALL_b as
select b.first_Branch_Cust_IP as Numerator_DWH,a.*
from ABT2_ALL as a
left join  TeraData.VBM374_USED_BRANCH_CUSTOMER as b
on a.lak_id=b.Branch_Cust_Nbr  and a.snif=b.Branch_Nbr
;
quit;

There is not sort here so why I get this error?

What should I do to solve it?

It is one to many merge

[LinusH]

SQL actually needs to sort the data to be able to perform a join.

You can see this if you add the (undocumented) PROC SQL option _method.

You probably run out of resources, disk or memory.

Check the available space on your saswork location.

You can add 

options fullstimer;

to get more information about resource consumption for your step.

[Ksharp]

It looks like your tables is from DataBase "TeraData" ,That means the SORT is not happened in SAS side ,while in TERA DB. SAS is just pass SQL code into TERA DB .
Talk to your DB Administrator .

[LinusH]

Actually, I think not.

NOTE: SAS threaded sort was used.

tells me that SAS does the sorting.

But from a performance perspective, the OP might want to have the sort and join happening in Teradata instead.

To make that happen, the ABT_ALL table should be uploaded to a Teradata temporarily table first

[Ronein]

Thanks friends.
The data set in teradara library cannot be sorted.
I want to ask if my query ask to sort it??? Or the sort is done in a temporary work data set?

[Ronein]

Thanks friends.
The data set in teradara library cannot be sorted.
I want to ask if my query ask to sort it??? Or the sort is done in a temporary work data set?p

[Tom]

---

@Ronein wrote:
Thanks friends.
The data set in teradara library cannot be sorted.
I want to ask if my query ask to sort it??? Or the sort is done in a temporary work data set?p

---

TERADATA does not store data as sorted.  That is not how it works.  But it can return the data in any sorted order you want, you just need to specify a BY statement (or ORDER BY in SQL syntax).

Your real problem is probably that SAS has to pull ALL of the data from the TERADATA table into your SAS session in order to do the join and the size of the TERADATA table is overwhelming your SAS server.

You could try using normal SAS syntax to see if that helps.

data want;
  merge my_sas_ds (in=in1)  teradata.my_td_table(keep=id some_other_variable);
  by id;
  if in1;
run;

Otherwise you will want to upload the key values from your SAS table to TERADATA so that you only need to pull out the observations you want from the TERADATA table.

[Ronein]

May you please show code that do your plan (Otherwise you will want to upload the key values from your SAS table to TERADATA so that you only need to pull out the observations you want from the TERADATA table.)?

[Kurt_Bremser]

How large is your dataset abt2_all?

I suspect that SAS retrieves the whole Teradata table and tries to sort it locally, so an early reduction to the rows (and columns) needed should help. It might even be possible to create a hash object for abt2_all plus the numerator variable, read the Teradata table sequentially and add the numerator whenever the keys are found in the hash.

Note: any type of join requires sorting; either the datasets are already sorted (e.g. for a DATA step MERGE with BY), will be sorted in memory (hash object) or sorted implicitly (as happens in your SQL).

[Patrick]

A simple Google search using your Note and Error message brought this up (you don't have enough disk space for the sort on the SAS side).
Usage Note 39705: Troubleshooting "Sort initialization failure" and "Sort execution failure" errors ...

You are joining a SAS table with a Teradata table. To do so SAS will first pull all rows from the Teradata table to the SAS side - hopefully only the required columns though. To know what SQL really gets executed add options OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;

If your SAS table is rather small and your Teradata table big then there are more efficient ways for doing this - for example first load your SAS table into a temporary Teradata table and then do the join fully in-database.

And yes, in order to do the join SAS will have to implicitly sort the data along your join condition.

@LinusH 

_method and _tree are now documented. 
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/p12ohgh32ffm6un13s7l2d5p9c8y.htm