Sorting Data

SPD Server supports implicit and explicit sorts. An implicit sort is unique to the server. Each time you submit a SAS statement with a BY clause, the server sorts your data, unless the table is already sorted or indexed by the BY column. All BY statements and WHERE statements that appear in a DATA step or SAS procedure are automatically passed to the server. The data returned by the server is a subset based on the WHERE statement, and that subset is implicitly sorted based on the BY statement. Because this happens, there is no need to precede the DATA step or procedures with a PROC SORT. However, if you want to perform an explicit sort, you can use PROC SORT.

Many SAS job streams are structured with code that alternates PROC SORT followed by another procedure invocation, where the PROC SORT step is needed only for the execution of the other procedure’s invocation. When sort order is relevant only to the following step, with SPD Server, you can eliminate the PROC SORT step and just use the BY clause in the procedure. This eliminates the extra data transfer (to PROC SORT from the server and then back from PROC SORT to the server) to store the sorted result. Even if the server performs the sort associated with the PROC SORT, there is extra data transfer. The data's round trip from the server to the SAS client and back can impose a substantial time penalty.

The following DATA step performs a server sort on the table column PRICE. There is no prior index for PRICE and there's no need for a PROC SORT step before the DATA step.

data first last;
   set sport.expraqs;
   by price;
   if first.price then output first;   
   if last.price then output last;
run;