Re: Proc means VS Proc SQL for summarizing

Diwakar:
Far be it from me to discourage use of SAS SQL, but I don't believe that
an isolated example can make the case for one method vs. another. PROC
MEANS (or, almost equivalently, PROC SUMMARY) by default calculates
statistics for multiple groupings of data. The NWAY option limits those
calculations to the full class level. In this case of a single class
variable, that shouldn't matter much, but with more variables in a CLASS
the computational overhead becomes more significant. You might try
adding the NWAY option to see if that makes a difference in execution
time.
S

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailtowner-sas-l@listserv.uga.edu]
On Behalf Of Diwakar_Sharma@mckinsey.com
Sent: Wednesday, July 11, 2007 9:56 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Proc means VS Proc SQL for summarizing


Hi SAS-L,

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;
class category;
types category;
output out=out_means
sum(sales)=sum_sales
;
run;
proc sql _METHOD;
create table out_sql as
select category , sum(sales) as sum_sales
from test_data
group by category;
quit; run;


Here is the log

242 proc means data=test_data MISSING NOPRINT;
243 class category;
244 types category;
245 output out=out_means
246 sum(sales)=sum_sales
247 ;
248 run;

NOTE: There were 407649 observations read from the data set
WORK.TEST_DATA.
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:

sqxcrta
sqxsumg
sqxsort
sqxsrc( WORK.TEST_DATA )
NOTE: Table WORK.OUT_SQL created, with 29 rows and 2 columns.

254 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 3.51 seconds
cpu time 1.54 seconds


254! run;

Could someone please let me know the reason of this difference ??

Best regards,
Diwakar Sharma
+================================================= ========+
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.
+================================================= ========+