Both of the queries
in the following PROC SQL step list the name, address, city, state,
and ZIP code of customers listed in the Sasuser.FrequentFlyers table.
However, the second query lists only this information for customers
who have earned more than 7000 points and used less than 3000 points.
When the PROC SQL statement
is submitted without the STIMER option, timing information for both
queries is written to the SAS log as a cumulative value for the entire
procedure.
proc sql;
select name, address, city, state, zipcode
from sasuser.frequentflyers;
select name, address, city, state, zipcode
from sasuser.frequentflyers
where pointsearned gt 7000 and pointsused lt 3000;
quit;
Note: Timing information for a
PROC SQL step is not written to the SAS log until a QUIT statement
is submitted or another PROC or DATA step is started.
Table 8.6 SAS Log
28 proc sql;
29 select name, address, city, state, zipcode
from sasuser.frequentflyers;
30 select name, address, city, state, zipcode
from sasuser.frequentflyers
31 where pointsearned gt 7000 and pointsused lt 3000;
32 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.34 seconds
cpu time 0.30 seconds
|
When the PROC SQL statement
is submitted with the STIMER option, timing information is written
to the SAS log for each SELECT statement.
proc sql stimer;
select name, address, city, state, zipcode
from sasuser.frequentflyers;
select name, address, city, state, zipcode
from sasuser.frequentflyers
where pointsearned gt 7000 and pointsused lt 3000;
quit;
Table 8.7 SAS Log
33 proc sql stimer;
NOTE: SQL Statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
34 select name, address, city, state, zipcode
from sasuser.frequentflyers;
NOTE: SQL Statement used (Total process time):
real time 0.22 seconds
cpu time 0.17 seconds
35 select name, address, city, state, zipcode
from sasuser.frequentflyers
36 where pointsearned gt 7000 and pointsused lt 3000;
NOTE: SQL Statement used (Total process time):
real time 0.25 seconds
cpu time 0.08 seconds
37 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.29 seconds
cpu time 0.03 seconds
|
Note: When the STIMER option is
used in PROC SQL, the exact wording of the Notes that are written
to the SAS log might vary for different versions of SAS.
Note: The STIMER option in PROC
SQL is useful when an operation can be accomplished in more than one
way and you are benchmarking each technique. Although factors such
as code readability and maintenance come into consideration, you might
also want to know which PROC SQL step runs the fastest.