a. title 'Maximum Salary for Each Job in every State';
proc sql;
select upcase(State) as State, JobTitle,
max(Salary) as MaxJobSalary
from sq.employee
where State is not null
group by calculated State, JobTitle
order by State;
quit;
title; b. title 'Employees with Highest Salary for their Job in every State';
proc sql;
select detail.EmployeeID, detail.EmployeeName, detail.State,
detail.JobTitle, detail.Salary format=dollar12.
from sq.employee as detail inner join
(select upcase(State) as State,
JobTitle, max(Salary) as MaxJobSalary
from sq.employee
where State is not null
group by calculated State, JobTitle) as summary
on detail.Jobtitle=summary.JobTitle and
upcase(detail.State)=Summary.State and
detail.Salary=Summary.MaxJobSalary
order by detail.State, detail.JobTitle;
quit;
title; I don't easily understand this program can anyone explain this !!!!
... View more
