Setting SQL Procedure Options 1 Controlling Processing PROC

Setting SQL Procedure Options 1

Controlling Processing PROC SQL options give you finer control over your SQL processes: Syntax checking without executing your code Expanding SQL statements to their fully-qualified values Restricting the number of rows processed Providing system utilization statistics for query tuning 2

Controlling Processing, Selected options Option Effect INOBS=n sets a limit of n rows from each source table that contributes to a query. OUTOBS=n restricts the number of rows that a query outputs (displays or writes to a table). NOSTIMER|STIMER controls whether or not PROC SQL writes resource utilization statistics to the SAS log. continued. . . 3

Controlling Processing, Selected options Option Effect PRINT|NOPRINT controls whether the results of a SELECT statement are displayed in the OUTPUT window. NONUMBER|NUMBER controls whether the row number is displayed as the first column in query output. NODOUBLE|DOUBLE controls whether the report is double -spaced. continued. . . 4

Use the RESET statement to add or change PROC SQL options without re-invoking the procedure RESET option(s); 5

Controlling Processing, Read ten rows from orion. Price_List proc sql inobs=10; title "orion. Price_List - INOBS=10"; select Product_ID, Unit_Cost_Price format=comma 8. 2, Unit_Sales_Price-Unit_Cost_Price as Margin format=comma 8. 2 from orion. Price_List ; quit; title; 6

Controlling Processing. Join all rows, but limit output to the 10 most profitable customers proc sql outobs=10; title "10 Most Profitable Customers"; select Customer_ID, sum(Unit_Sales_Price-Unit_Cost_Price) as Profit_2007 format=comma 8. 2 from orion. Price_List as p, orion. Order_Fact as o where p. Product_ID=o. Product_id and year(Order_date) =2007 group by Customer_ID order by Profit_2007 desc; quit; title; 7

proc sql number outobs=10; title "Top 10 Employee's 2007 Charitable Donations"; select Employee_Name, Sum(Qtr 1, Qtr 2, Qtr 3, Qtr 4) as Donation_Total from orion. Employee_Addresses as a, orion. Employee_donations as d where a. Employee_ID=d. Employee_ID order by Donation_Total desc, Employee_Name ; reset nonumber outobs=9; title "Top 9 Employee's 2007 Charitable Donations"; select Employee_Name, Sum(Qtr 1, Qtr 2, Qtr 3, Qtr 4) as Donation_Total from orion. Employee_Addresses as a, orion. Employee_donations as d where a. Employee_ID=d. Employee_ID order by Donation_Total desc, Employee_Name ; quit; title;