BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Bhagyalakshmi
Fluorite | Level 6
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 !!!!   

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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.

Patrick_0-1711000076686.png

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 solution in original post

2 REPLIES 2
SASKiwi
PROC Star

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
Opal | Level 21

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.

Patrick_0-1711000076686.png

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.

 

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 663 views
  • 2 likes
  • 3 in conversation