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 !!!!
Assuming you understand how a inner join works may-be the following helps you to understand.
Assume the first SQL creates a view:
proc sql;
create table view summary_view as
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;
You then could use this view in your 2nd SQL for a join.
proc sql;
select detail.EmployeeID, detail.EmployeeName, detail.State,
detail.JobTitle, detail.Salary format=dollar12.
from
sq.employee as detail
inner join
summary_view 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;
With an inline view the only difference is that you don't create the view first explicitly but just use the view creation code directly within brackets. It's just about replacing the highlighted view name below with the code that creates the view.
proc sql;
select detail.EmployeeID, detail.EmployeeName, detail.State,
detail.JobTitle, detail.Salary format=dollar12.
from
sq.employee as detail
inner join
(
/* summary_view code */
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;
For the inline view the code bit we can skip is the ORDER BY clause as this is only required for returning the final result set.
What is it about program B that you don't understand? It is an example of having a main query of employees being joined to a sub-query that is identifying the maximum salaries for each state and job title. The purpose of the query is to report on all employees who are paid the maximum salary within each state and job. title.
Assuming you understand how a inner join works may-be the following helps you to understand.
Assume the first SQL creates a view:
proc sql;
create table view summary_view as
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;
You then could use this view in your 2nd SQL for a join.
proc sql;
select detail.EmployeeID, detail.EmployeeName, detail.State,
detail.JobTitle, detail.Salary format=dollar12.
from
sq.employee as detail
inner join
summary_view 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;
With an inline view the only difference is that you don't create the view first explicitly but just use the view creation code directly within brackets. It's just about replacing the highlighted view name below with the code that creates the view.
proc sql;
select detail.EmployeeID, detail.EmployeeName, detail.State,
detail.JobTitle, detail.Salary format=dollar12.
from
sq.employee as detail
inner join
(
/* summary_view code */
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;
For the inline view the code bit we can skip is the ORDER BY clause as this is only required for returning the final result set.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.