Automate SAS code to wait for sorted datasets

[ChrisNZ]

The SP library is visible as any other library from EG, and likewise the SORTED data set.

So is the sort done? It just took a couple of minutes?

Or did the code not run? Did you look at the log?

[  Edit: Wait a second. If you sort by ID, surely you want to merge by ID. Why do you drop ID?  ]

[JJP1]

Hi @ChrisNZ ,

kindly find details below .

Iam unable to see The SP library under SAS EG librraies.

So is the sort done? It just took a couple of minutes?

i have started at 11:57 UK time ,but now time is 12:57.still it is running with SPDE option.

and @Patrick and @Kurt_Bremser just to let you know with tagsort option sort query on large dataset started at 11:33 UK timing ,still query is running please

[  Edit: Wait a second. If you sort by ID, surely you want to merge by ID. Why do you drop ID?  ]

yes chris .i need to sort it on ID column but i don't want that column after sorting done in output please.

as this is child table we will be taking ID column from parent table for this XX.BB tables please

Sorry i almost consumed most of all of  your time .but the query is still running

[ChrisNZ]

Are you running 2 sorts at the same time? And then you complain of slow speed and lack of space? Don't do that. Refresh the libraries in EG.

[Patrick]

@JJP1 

And just to add to my previous statement: You might want to read in the docu what it says about Proc Sort and Threads.

https://go.documentation.sas.com/?docsetId=proc&docsetTarget=p02bhn81rn4u64n1b6l00ftdnxge.htm&docset... 

So if the THREAD option is set then Proc Sort will do such a "split-sort" already on its own internally and there is really no need to code specifically for it. 

But also as the documentation states you can either have THREADED sorting or TAGSORT - but not both. Threaded sorting will likely improve performance while TAGSORT will reduce work space requirements (so less risk of out of space conditions).

TAGSORT will eventually also outperform a threaded sort if disk I/O is the bottleneck and the sort key portion of the table is much smaller in storage requirements than the whole table. I guess only testing will show what's better in your environment and with your actual table and sort keys.

[Kurt_Bremser]

Whenever you run into "insufficient disk space" while doing a join in SQL (or experience unexpectedly bad performance), you positively need to test the basic SAS method of sorting and doing a data step merge before you hare off into really complicated coding, provided you do not need the SQL capability of creating a cartesian product. If a simple proc sort of a dataset is not possible (even when using the tagset option), then you need to either work on your infrastructure or go into complex coding.

But whatever you do, sorting in sequence is to be preferred over sorting in parallel.