Help using Base SAS procedures

Proc sql vs data step.

Reply
N/A
Posts: 0

Proc sql vs data step.

To me Proc sql seems slow when doing a query against a SAS table, compared to doing the processing in a data step.

The code will be more compact and easier to write in Proc sql, but the speed ...

What's your impression regarding this?
Trusted Advisor
Posts: 2,115

Re: Proc sql vs data step.

Posted in reply to deleted_user
As they say, "your mileage may vary."

There are some things that are quicker and others slower. I'll take compact and easier to write anytime and boost the hardware... We use SQL on huge Medicare claims in SAS datasets and the performance is acceptable.
Super Contributor
Posts: 474

Re: Proc sql vs data step.

When dealing with small amount of data, I always use SQL.

Medium to average, I may still consider SQL for simple tasks, otherwise it's datastep/procs.

For large to huge amount of data, I'll stick to datastep/procs, unless I got on my hands some N to N match to perform.

Datastep gives you a lot of more control on how things will be performed.
With SQL, although you may influence the optimizer, it's kinda of a "black box".

But then again, it depends on your hardware architecture, on the amount of data, and on what you are trying to do with it.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt
Occasional Contributor
Posts: 7

Re: Proc sql vs data step.

Posted in reply to deleted_user

Same with me here. I have also seen data Step executing faster than PROC SQL. But, Data Step may produce more lines of code than PROC SQL.

Trusted Advisor
Posts: 3,212

Re: Proc sql vs data step.

Posted in reply to deleted_user

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.  

---->-- ja karman --<-----
Super User
Posts: 19,770

Re: Proc sql vs data step.

Posted in reply to deleted_user

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.

Ask a Question
Discussion stats
  • 5 replies
  • 265 views
  • 0 likes
  • 6 in conversation