When should I use Use PROC SQL instead of PROC SORT for sorting in tasks

[SanjayM]

Please help me on this option Use PROC SQL instead of PROC SORT for sorting in tasks.

When should I select this. My data base is Oracle. I am using sort option in Filter and Query task but not explicitly using Sort task.

Should I check this option.

[Doc_Duke]

If you use PROC SORT, the sorting will be done on one SAS dataset on your SAS server.

If you use SQL, the SORT may be done on the Oracle server, depending on a the complexity of the syntax and whether or not other filter or query options (like functions in the expression builder) can be translated into Oracle's flavor of SQL or not. (SAS tries to resolve the syntax into something that Oracle can do and just get the results back at the end.).

Personally, I do it both places. If all I need is a sort, I do it in the SORT task; if I am doing other transformations, I'll let SQL do it (the latter case is more common). If I've lot lots of data (hundreds of thousands of records), then I am more thoughtful in my choice (and read the SAS/Access for Oracle manual).

Doc Muhlbaier

[jonam]

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.

[ChrisHemedinger]

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