Help using Base SAS procedures

Why is Proc SQL so slow with a large data set?

Reply
N/A
Posts: 0

Why is Proc SQL so slow with a large data set?

Hi, I'm trying to get some practice with Proc SQL. One thing that strikes me is that it's so slow with a large data set (5GB++). I experiment the old SAS and it works significantly faster.

so, my question is, in which situations is SQL better than SAS steps?

My program is calculating the total value for each of the groups...

Thanks a lot
SAS Super FREQ
Posts: 8,743

Re: Why is Proc SQL so slow with a large data set?

In some instances calculating totals by grouping can sometimes cause a "remerge", depending on how you have coded your query. You might find that PROC MEANS or PROC TABULATE is much faster to calculate group totals. If you need detail lines with group totals, then PROC REPORT will work.

If you see this note in your SAS log with PROC SQL, to me this is a clue that the SQL might not be the best approach or that there's a problem with your GROUP BY clause:
[pre]
NOTE: The query requires remerging summary statistics back with the
original data.
[/pre]

See this Tech Support note for more information about the above log message.
http://support.sas.com/kb/4/308.html

cynthia
Frequent Contributor
Posts: 89

Re: Why is Proc SQL so slow with a large data set?

Good explanation

Super User
Posts: 9,682

Re: Why is Proc SQL so slow with a large data set?

Hi. You can use ' create index ' in proc sql to index your dataset to enhance speed.
N/A
Posts: 0

Re: Why is Proc SQL so slow with a large data set?

Hi, thank you all for writing back. It's very useful to me.

Can you suggest a Proc SQL book that's not too thick (< 300++ pages)? Actually, I've searched the SAS Bookstore, as well as elsewhere online. There are many titles coming up, but I don't know which one fits me the most given time constraints.

I've looked into several titles, of which I particularly like PROC SQL by EXAMPLE. IMHO, it's perhaps the best one and designed to be read, understood, and applied, thanks to its dual, side-by-side comparison of SAS traditional steps and Proc SQL. It appears trivial, but, actually, it helps learning much faster, much more efficiently, etc... than reading a title that separates itself completely from the SAS conventionals.

Has anyone of you heard of this book?

Thanks a lot
N/A
Posts: 1

Re: Why is Proc SQL so slow with a large data set?

Hi,

The best SAS SQL "book" I have seen is in the SAS Advanced Certification manual. It has one (out of four) section dedicated to SQL. It does an excellent job of explaining SQL, from the basics to more advanced structures.

Good luck!
Robin
N/A
Posts: 0

Re: Why is Proc SQL so slow with a large data set?

Hi, thanks for the book recommendation! I have purchased this prep guide. It's very, very detailed, much more so than the SAS Proc SQL User's Guide. The material is quite thick; however, the chapter exercises are far more simple than what's covered in the chapter, and what comes up in practice. I'm not sure if the real exam will be like this.

Thanks
Ask a Question
Discussion stats
  • 6 replies
  • 3348 views
  • 1 like
  • 5 in conversation