If your data is in RDBMS(oracle in this case), then definitely go for sort @ oracle. the reason being.
1. If your sorting variables are defined as primary key or if it has index on the sorting key. Then oracle automatically can leverage the index to take less time to sort the data.
2. In case of data residing in RDBMS, when you try to do the sort in SAS. i believe its an additional task, because 1) data transferring without sort 2) once all obs are ready then only it sorts the data in SAS. 3) after sorting it writes to the sas dataset. whereas if you do in RDBMS all the processing is done at RDBMS, so the result is directly created as sas dataset.
Apart from these, RDBMS has some facilities that helps querying the data, processing the data better in that environment due to native SQL it use to process the data.
Hope this helps.
The Filter and Sort task, and the Query Builder, always use PROC SQL and *should* push the work to the database.
Other tasks that require an implicit sort (such as a Summary Statistics task with a By variable) will automatically defer to the database to perform a sort. That is, instead of generating a PROC SORT or SQL when generating task code, EG will rely on the SAS procedure BY statement to trigger an "order by" instruction to the database when fetching the required data. This happens automatically when EG detects that the source data is coming from a DBMS. (This optimization is in EG 4.2 and 4.3.)
Chris
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.