proc sql of Building a Complex Query Using In-Line Views

[Bhagyalakshmi]

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 !!!!   

[SASKiwi]

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.

[Patrick · Accepted solution]

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.