Grouping Summary Results Group by clause proc sql

Grouping Summary Results

Group by clause proc sql; select Employee_Gender, avg(Salary) as Average from orion. Employee_Payroll where Employee_Term_Date is missing group by Employee_Gender ; quit; 2

Grouping Data Use the GROUP BY clause to: Classify the data into groups based on the values of one or more columns Calculate statistics for each unique value of the grouping columns 3

Grouping Data proc sql; select male, chd 10 yr, avg(chol) as avg_chol, n(chol) as n_chol from s 5238. chd 5238 group by male, chd 10 yr ; quit; 4

Determine the total number of employees in each department. proc sql; select Department, count(*) as Count from orion. Employee_Organization group by Department ; quit; 5

Calculate each male employee’s salary as a percentage of all male employees’ salaries. Display the employee ID, salary, and percentage in decreasing order of percentage. proc sql; title "Male Employee Salaries"; select Employee_ID, Salary format=comma 12. , Salary / sum(Salary) format=percent 6. 2 from orion. Employee_Payroll where Employee_Gender="M" and Employee_Term_Date is missing order by 3 desc ; quit; title; . . . 6

Selecting Groups with the HAVING Clause The WHERE clause is processed before a GROUP BY clause and determines which individual rows are available for grouping. The HAVING clause is processed after the GROUP BY clause and determines which groups will be displayed. 7

Selecting Groups with the HAVING Clause -- Display the names of the departments and the number of employees for departments with 25 or more employees proc sql; select Department, count(*) as Count from orion. Employee_Organization group by Department having Count ge 25 order by Count desc ; quit; 8

The payrollmaster data set. libname misc "&path/Misc. Data"; proc contents data=misc. payrollmaster; run; proc freq data=misc. payrollmaster; tables jobcode; run; 9

Having clause libname misc "&path/Misc. Data"; proc sql; select jobcode, avg(salary) as avg from misc. payrollmaster group by jobcode having Avg>40000 order by jobcode ; quit 10