BookmarkSubscribeRSS Feed
SanjayM
Calcite | Level 5
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.
3 REPLIES 3
Doc_Duke
Rhodochrosite | Level 12
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
Calcite | Level 5

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
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1127 views
  • 0 likes
  • 4 in conversation