SAS Program Testing and Performance Options
Explore the various testing and performance options available in SAS programming, including controlling SQL statement execution, capturing performance statistics, benchmarking system performance, and utilizing STIMER and FULLSTIMER options for detailed resource analysis. Learn how to optimize program efficiency and identify potential bottlenecks affecting CPU, memory, and I/O performance.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Testing and Performance Options Option Effect controls whether or not submitted SQL statements are executed. EXEC|NOEXEC reports performance statistics in the SAS log for each SQL statement. NOSTIMER|STIMER NOERRORSTOP| ERRORSTOP makes PROC SQL enter syntax-check mode after an error occurs; usually used in batch and non-interactive submissions. 2
Testing and Performance Options Display the columns that are retrieved when you use SELECT * in a query, and display any macro variable resolutions, but do not execute the query. %let big=100000; proc proc sql sql feedback noexec; select * from orion.Employee_payroll where salary>&big; quit quit; 3
Performance Benchmarking System performance issues are usually caused by bottlenecks in one of three major resources: CPU Memory Input/Output (I/O) An overload of any one of these resources can significantly increase the elapsed time required to execute your program. 4
Performance Benchmarking You can use the STIMER or FULLSTIMER options to gather information on how your SAS programs use CPU, memory, and I/O. OPTIONS STIMER; OPTIONS FULLSTIMER; The STIMER SAS system option causes SAS to print performance statistics in the SAS log for each DATA or PROC step executed. The FULLSTIMER option provides greater detail in performance reporting. Not all statistics are available on all operating systems, so the results might differ between operating environments. 5
Performance Benchmarking The STIMER option can also be specified as a PROC SQL option: proc sql stimer; When used in conjunction with the STIMER or FULLSTIMER SAS system option, the PROC SQL STIMER option provides CPU, memory, and I/O performance information for each individual statement executed by PROC SQL during a single invocation. This enables a more granular analysis of resource utilization. 6
Testing and Performance Options Example: Capture performance statistics for a complex query. options fullstimer; proc proc sql sql stimer; select distinct catx(' ',scan(Employee_Name,2 2,','), scan(Employee_Name,1 1,',')) format=$25. as Manager,City from orion.Order_Fact as of, orion.Product_Dim as pd, orion.Employee_Organization as eo, orion.Employee_Addresses as ea where of.Product_ID=pd.Product_ID and of.Employee_ID=eo.Employee_ID and ea.Employee_ID=eo.Manager_ID and Product_Name contains 'Expedition Zero' and year(Order_Date)=2003 and eo.Employee_ID ne 99999999 ; quit quit; 2003 99999999 7
Benchmarking Guidelines Elapsed time is affected by concurrent tasks and should not normally be used for benchmarking. Always benchmark your programs in separate SAS sessions. If benchmarking is done on different methods within a single SAS session, statistics for the second method can be misleading. SAS might retain modules loaded into memory or the operating system might cache data read from a disk that was used in prior steps. 8 continued...
Benchmarking Guidelines Run each program multiple times and average the performance statistics. Use realistic data for tests. Method A could be much more efficient than Method B when applied to small tables, but much less efficient on large tables. 9
SAS had been running about an hour. options fullstimer; proc proc sql sql stimer; create table counts as select app_id,count(app_id) from kag.train group by app_id ; quit quit;
