General form, SELECT
statement for inner join:
SELECT column-1<,...column-n>
FROM table-1
| view-1, table-2 | view-2<,...table-n
| view-n>
WHERE join-condition(s)
<AND other
subsetting condition(s)>
<other
clauses>;
join-condition(s)
refers to one or more
expressions that specify the column or columns on which the tables
are to be joined.
other subsetting condition(s)
refers to optional
expressions that are used to subset rows in the query results.
<other clauses>
refers to optional
PROC SQL clauses.
|
where table1.id = table2.empid
proc sql;
select one.x, a, b
from one, two
where one.x = two.x
proc sql;
select one.*, b
from one, two
where one.x = two.x;
proc sql;
select one.x as ID, two.x, a, b
from one, two
where one.x = two.x;
proc sql; title 'Employee Names and Job Codes'; select staffmaster.empid, lastname, firstname, jobcode from sasuser.staffmaster, sasuser.payrollmaster where staffmaster.empid=payrollmaster.empid;
proc sql; title 'Employee Names and Job Codes'; select s.empid, lastname, firstname, jobcode from sasuser.staffmaster as s, sasuser.payrollmaster as p where s.empid=p.empid;
from sasuser.staffmaster s, sasuser.payrollmaster p
Table aliases are required
when...
|
Example
|
---|---|
a table is joined to
itself (called a self-join or reflexive join)
|
from airline.staffmaster as s1, airline.staffmaster as s2 |
you need to reference
columns from same-named tables in different libraries
|
from airline.flightdelays as af, work.flightdelays as wf where af.delay > wf.delay |
Table
|
Relevant Columns
|
---|---|
Sasuser.Staffmaster
|
EmpID, LastName, FirstName,
State
|
Sasuser.Payrollmaster
|
EmpID, JobCode, DateOfBirth
|
proc sql outobs=15; title 'New York Employees'; select substr(firstname,1,1) || '. ' || lastname as Name, jobcode, int((today() - dateofbirth)/365.25) as Age from sasuser.payrollmaster as p, sasuser.staffmaster as s where p.empid = s.empid and state='NY' order by 2, 3;
select substr(firstname,1,1) || '. ' || lastname as Name, jobcode, int((today() - dateofbirth)/365.25) as Age
where p.empid =
s.empid
and state='NY'
NY
. You do not need to
prefix the column name State with a table name, because State occurs
in only one of the tables.
proc sql outobs=15; title 'Avg Age of New York Employees'; select jobcode, count(p.empid) as Employees, avg(int((today() - dateofbirth)/365.25)) format=4.1 as AvgAge from sasuser.payrollmaster as p, sasuser.staffmaster as s where p.empid = s.empid and state='NY' group by jobcode order by jobcode;