Tuesday 19 October 2010

NOTE: More on sorting and SQL

Further to my recent post on telling SAS that your data is pre-sorted, it's worth mentioning the SORTVALIDATE system option. This yes/no option tells SAS whether the SORT procedure should verify if a data set is sorted according to the variables in the BY statement when SORTEDBY has been used. There's an entertaining SAS Global Forum paper on this general subject entitled "Dear Miss SAS/Answers: A Guide to Sorting Your Data".

It's a subject worth being on top of because it can save a great deal of time and resource for your jobs.

Finally, if you're using the SORTEDBY option to avoid sorting being done within SQL joins, remember that you can you use PROC SQL's _method option to show what sorting activity is or isn't being done. Kirk Lafler presented a good paper on this undocumented feature at SAS Global Forum 2008.