Performs statistical summary calculations.
Restriction: | A summary function cannot appear in an ON clause or a WHERE clause. |
See: | GROUP BY Clause |
HAVING Clause | |
SELECT Clause | |
table-expression | |
Examples: | Creating a View from a Query's Result |
is one of the following:
arithmetic mean or average of values
number of nonmissing values
corrected sum of squares
coefficient of variation (percent)
largest value
smallest value
number of missing values
is the two-tailed p-value for Student's t statistic, T with n – 1 degrees of freedom.
range of values
standard deviation
standard error of the mean
sum of values
sum of the WEIGHT variable values (footnote1)
Student's t value for testing the hypothesis that the population mean is zero
uncorrected sum of squares
variance
specifies that only the unique values of an SQL expression be used in the calculation.
specifies that all values of an SQL expression be used in the calculation. If neither DISTINCT nor ALL is specified, then ALL is used.
is described in sql-expression.
NOTE: The query requires remerging summary statistics back with the original data.
proc sql outobs=10; title 'Salary Information'; title2 '(First 10 Rows Only)'; select IdNumber, Jobcode, Salary, avg(salary) as AvgSalary from proclib.payroll group by jobcode;
proc sql outobs=10; title 'Average Salary for Each Jobcode'; select Jobcode, avg(salary) as AvgSalary from proclib.payroll group by jobcode;
Number
, Average
Age
, and Average Salary
columns.
The first pass provides everything that PROC SQL needs to resolve
the HAVING clause, so no remerging is necessary.
proc sql outobs=10; title 'Summary Information for Each Jobcode'; title2 '(First 10 Rows Only)'; select Jobcode, count(jobcode) as number label='Number', avg(int((today()-birth)/365.25)) as avgage format=2. label='Average Age', avg(salary) as avgsal format=dollar8. label='Average Salary' from proclib.payroll group by jobcode having avgage ge 30;
proc sql outobs=10; title 'Employees who Earn More than the'; title2 'Average for Their Jobcode'; title3 '(First 10 Rows Only)'; select Jobcode, Salary, avg(salary) as AvgSalary from proclib.payroll group by jobcode having salary > AvgSalary;
data summary; input x; datalines; 32 86 49 49 ;
proc sql; title 'Percentage of the Total'; select X, (100*x/sum(X)) as Pct_Total from summary;
Percentage of the Total x Pct_Total ------------------- 32 14.81481 86 39.81481 49 22.68519 49 22.68519
proc sql; select jobcode, salary, avg(salary) as avsal from proclib.payroll group by jobcode having salary > avsal;
proc sql; select IdNumber, jobcode, avg(salary) as avsal from proclib.payroll group by jobcode;