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

[deleted_user]

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

[Cynthia_sas]

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

[venkatnaveen]

Good explanation

[Ksharp]

Hi. You can use ' create index ' in proc sql to index your dataset to enhance speed.

[deleted_user]

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

[RobinY]

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

[deleted_user]

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