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.
... View more
