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
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
to get more information about resource consumption for your step.
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
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.
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).
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.
_method and _tree are now documented.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.