PROC SQL?

Accepted Solution Solved
Reply
Super Contributor
Posts: 309
Accepted Solution

PROC SQL?

Could someone explain the following program?  I don't understand the red font.  Thanks.

 

Proc SQL;

select 
   Jobcode,   
   Salary,    
   (select avg(Salary)  
   from WORK.PILOTS as P1       
   where P1.Jobcode=P2.Jobcode) as Avg   
from WORK.PILOTS as P2  
order by Id;

run;      


Accepted Solutions
Solution
‎11-29-2016 01:33 PM
Respected Advisor
Posts: 4,801

Re: PROC SQL?

(select avg(Salary) from WORK.PILOTS as P1 where P1.Jobcode=P2.Jobcode) is the subquery returning a single value to the main query.

as Avg sets the name you want to give that value in the main query

from WORK.PILOTS as P2 is the FROM clause of the main query

 

hth

PG

View solution in original post


All Replies
Frequent Contributor
Posts: 102

Re: PROC SQL?

This does not appear to be a valid query. Table P2 is not defined in the sub-query in parentheses. This syntax should return an error to that effect--table P2 was not defined in the sub-query.
Respected Advisor
Posts: 4,801

Re: PROC SQL?

Your query contains a correlated subquery that calculates the average Salary for the Jobcode to which each Id belongs. It is equivalent to the simpler (and more efficient) :

 

Proc SQL;
select 
   Id,
   Jobcode,   
   Salary,    
   avg(Salary) as Avg    
from WORK.PILOTS  
group by Jobcode
order by Id;
quit;

I added Id to the result set.

PG
Super Contributor
Posts: 309

Re: PROC SQL?

I know this is the easier way, but I am trying to understand the codes list above.  Thanks.

Respected Advisor
Posts: 4,801

Re: PROC SQL?

(select avg(Salary) from WORK.PILOTS as P1 where P1.Jobcode=P2.Jobcode) is a subquery that returns a single result: the average salary corresponding to P2.Jobcode, the Jobcode in the main query. It works just as if the subquery was run once for every value of Jobcode in the main query.

PG
Super Contributor
Posts: 309

Re: PROC SQL?

I am so lost here.  I guess we treat (select avg(Salary)   from WORK.PILOTS as P1   where P1.Jobcode=P2.Jobcode) first, then work on

as Avg    from WORK.PILOTS as P2   later.   Am I right?  But why there are so much "Avg" coming up?  Why WORK.PILOTS sometimes is P1 and then P2?

Solution
‎11-29-2016 01:33 PM
Respected Advisor
Posts: 4,801

Re: PROC SQL?

(select avg(Salary) from WORK.PILOTS as P1 where P1.Jobcode=P2.Jobcode) is the subquery returning a single value to the main query.

as Avg sets the name you want to give that value in the main query

from WORK.PILOTS as P2 is the FROM clause of the main query

 

hth

PG
Super Contributor
Posts: 309

Re: PROC SQL?

Thanks for your explaination. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 321 views
  • 0 likes
  • 3 in conversation