Desktop productivity for business analysts and programmers

Sort Order and Merge By

Reply
Contributor
Posts: 25

Sort Order and Merge By

Hello Team,

 

Greetings

 

We have 2 datasets. On these 2 datasets we are performing some merge operations.

 

What will be impact if

1. input data sets are in Sorted order

2. Input data sets are not in sorted order

 

Thanks In advance

Super User
Posts: 7,405

Re: Sort Order and Merge By

For a DATA step merge, sorting before the merge is mandatory. Exception: if the datasets are stored in SPDS, then the sort is done automatically (this does not reduce the overall resource consumption, you just don't need to write and execute the SORT step on your own)

If you use SAS SQL, no sorting is necessary, as SQL does this on the fly. But there is a big caution: with large datasets, the utilty file of the SQL step can grow out of proportion, and the random accesses to this file will be much less performant than the combined SORT/DATA steps. Perfomance penalties of orders of magnitude have been observed.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,382

Re: Sort Order and Merge By

Addition to @KurtBremser exception list: implicit sort is also being done for SPDE and most SAS/ACCESS to RDBMS engines.
Data never sleeps
Super User
Posts: 5,382

Re: Sort Order and Merge By

Addition to @KurtBremser exception list: implicit sort is also being done for SPDE and most SAS/ACCESS to RDBMS engines.
Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 265 views
  • 1 like
  • 3 in conversation