02-24-2010 09:43 AM
02-24-2010 10:14 AM
02-24-2010 12:57 PM
11-06-2013 03:16 PM
There are major differences in the datastep approach vs SQL approach.
Touching all data the datastep should be faster as doing one ordered speed up IO stream on the background. But it is single threaded.
The single threaded approach is required as the data is processed ordered. You have lag/retain fucntionality aside first/last (missing in SQL).
Doing small part of work SQL can win as this will work better on isolated records.
It will execute Multi-threaded (more processes) and cause more randomness in IO causing wait-seek time.
If you have SAS dataset processed with SQL you can also specify indexes and constraints like every DBMS.
Also modfiy records in place instead of replacing datasets is possible.
11-06-2013 03:39 PM
My personal rule(s):
If I'm working with the data on a row level then I use a data step, if I'm working with the columns, doing aggregates/counts I use SQL.
I generally do merges with SQL, unless I need to identify the incoming data set then I use a DATA STEP.
If data is on a server and there's the option, I'll use SQL Pass Through for bigger datasets (anything that takes longer than 30 secs) whenever possible.