Date: Thu, 12 Jul 2007 22:52:33 +1000
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: David Johnson <d@DKVJ.BIZ>
Subject: Re: Proc means VS Proc SQL for summarizing
Content-Type: text/plain; charset="us-ascii"
I've sniped at these comparisons before because as they stand I don't think
they prove anything.
Seeing Sig discount it as well gives me the courage again to suggest this is
not a good comparison.
On the face of it, you ran two steps, and got different timing. Have you
. Something may have been using CPU or I/O on your system during the first
test, and had gone by the second? What would that do to the timing?
. The second test is using data that was read a moment ago, and is probably
still largely resident in memory. What will that do to the I/O part of the
I see someone has also challenged your use of TYPES instead of NWay, which
would have been my preference, but whether this is probative, only another
test might show.
It seems aeons ago I was shown how to do these tests, and it involved a
minimum of three, and perhaps more iterations of each step, one after
another (not interspersed) and taking a mean value for the run and CPU times
to determine expected run times. I was also taught to run some other large
step between the two processes to flush the memory and remove the advantage
given to the second step.
This gives the memory advantage to second and subsequent executions and
equally affects each step. Based on this you now have a foundation for
stating one has a benefit over the other.
It may sound quite fussy, but when trying to compare processes to unload
data from a SQL server some time ago, I set up a weekend run using SQL Pass
through, Library access and SQL access with various options set and changed.
Running over 60 hours gave me lots of runs I trapped from a log parse and
gave me a clear idea of what worked without being concerned with contention
with other users, backups or network processes. The final choice still had
to run in peak time, but I was quite certain that at the best of time it was
the best, and at the worst time it would perform better than the
alternatives. Bear in mind that queuing theory demonstrates that if you
place a slow job in the path, everything else will slow down, and your slow
job will crawl. Making it efficient affects and benefits everybody, most
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of
Sent: Wednesday, 11 July 2007 11:56 PM
Subject: Proc means VS Proc SQL for summarizing
I used to be a firm believer that whatever Proc SQL can do, SAS (DATA &
Proc) can do better.
Recently I tried performing a summary using both Proc means and Proc SQL,
and found Proc SQL taking less time than PROC means.
Here is the code I used
proc means data=test_data MISSING NOPRINT;
proc sql _METHOD;
create table out_sql as
select category , sum(sales) as sum_sales
group by category;
Here is the log
242 proc means data=test_data MISSING NOPRINT;
243 class category;
244 types category;
245 output out=out_means
NOTE: There were 407649 observations read from the data set
NOTE: The data set WORK.OUT_MEANS has 29 observations and 4 variables.
NOTE: PROCEDURE MEANS used (Total process time):
real time 6.26 seconds
cpu time 6.96 seconds
249 proc sql _METHOD;
250 create table out_sql as
251 select category , sum(sales) as sum_sales
252 from test_data
253 group by category;
NOTE: SQL execution methods chosen are:
sqxsrc( WORK.TEST_DATA )
NOTE: Table WORK.OUT_SQL created, with 29 rows and 2 columns.
NOTE: PROCEDURE SQL used (Total process time):
real time 3.51 seconds
cpu time 1.54 seconds
Could someone please let me know the reason of this difference ??
This message may contain confidential and/or privileged
information. If you are not the addressee or authorized to
receive this for the addressee, you must not use, copy,
disclose or take any action based on this message or any
information herein. If you have received this message in
error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.