Table
|
Relevant Columns
|
---|---|
Sasuser.Flightschedule
identifies the crew who flew to Copenhagen on March 4, 2000
|
EmpID, Date, Destination
|
Sasuser.Staffmaster
identifies the names and states of residence for the employees
|
EmpID, FirstName, LastName,
State
|
Sasuser.Payrollmaster
identifies the job categories for the employees
|
EmpID, JobCode
|
Sasuser.Supervisors
identifies the employees who are supervisors
|
EmpID, State, JobCategory
|
Task
|
List the names of supervisors
for the crew on the flight to Copenhagen on March 4, 2000.
|
---|---|
Data
|
Sasuser.Flightschedule
(EmpID, Date, Destination)
Sasuser.Staffmaster
(EmpID, FirstName, LastName, State)
Sasuser.Payrollmaster
(EmpID, JobCode)
Sasuser.Supervisors
(EmpID, State, JobCategory)
|
c
has been assigned
to the in-line view. Query 2 returns to Query 3 the job category
and state for each crew member. Query 3 selects the employee ID numbers
for supervisors whose job category and state match the job category
and state of a crew member.
proc sql; select empid from sasuser.supervisors as m, (select substr(jobcode,1,2) as JobCategory, state from sasuser.staffmaster as s, sasuser.payrollmaster as p where s.empid=p.empid and s.empid in (select empid from sasuser.flightschedule where date='04mar2000'd and destination='CPH')) as c where m.jobcategory=c.jobcategory and m.state=c.state; |
|
proc sql; select firstname, lastname from sasuser.staffmaster where empid in (select empid from sasuser.supervisors as m, (select substr(jobcode,1,2) as JobCategory, state from sasuser.staffmaster as s, sasuser.payrollmaster as p where s.empid=p.empid and s.empid in (select empid from sasuser.flightschedule where date='04mar2000'd and destination='CPH')) as c where m.jobcategory=c.jobcategory and m.state=c.state); |
|
Task
|
List the names of supervisors
for the crew on the flight to Copenhagen on March 4, 2000.
|
---|---|
Data
|
Sasuser.Flightschedule
(EmpID, Date, Destination)
Sasuser.Staffmaster
(EmpID, FirstName, LastName, State)
Sasuser.Payrollmaster
(EmpID, JobCode)
Sasuser.Supervisors
(EmpID, State, JobCategory)
|
proc sql; select distinct e.firstname, e.lastname from sasuser.flightschedule as a, sasuser.staffmaster as b, sasuser.payrollmaster as c, sasuser.supervisors as d, sasuser.staffmaster as e where a.date='04mar2000'd and a.destination='CPH' and a.empid=b.empid and a.empid=c.empid and d.jobcategory=substr(c.jobcode,1,2) and d.state=b.state and d.empid=e.empid; |
|
b
, then e
. The table is read
the first time (alias b
) to look up the states of the
Copenhagen crew members, the second time (alias e
) to look up the
names of the supervisors.
Task
|
List the names of supervisors
for the crew on the flight to Copenhagen on March 4, 2000.
|
---|---|
Data
|
Sasuser.Flightschedule
(EmpID, Date, Destination)
Sasuser.Staffmaster
(EmpID, FirstName, LastName, State)
Sasuser.Payrollmaster
(EmpID, JobCode)
Sasuser.Supervisors
(EmpID, State, JobCategory)
|
/* Find the crew for the flight. */ proc sort data=sasuser.flightschedule (drop=flightnumber) out=crew (keep=empid); where destination='CPH' and date='04MAR2000'd; by empid; run; /* Find the State and job code for the crew. */ proc sort data=sasuser.payrollmaster (keep=empid jobcode) out=payroll; by empid; run; proc sort data=sasuser.staffmaster (keep=empid state firstname lastname) out=staff; by empid; run; data st_cat (keep=state jobcategory); merge crew (in=c) staff payroll; by empid; if c; jobcategory=substr(jobcode,1,2); run; /* Find the supervisor IDs. */ proc sort data=st_cat; by jobcategory state; run; proc sort data=sasuser.supervisors out=superv; by jobcategory state; run; data super (keep=empid); merge st_cat(in=s) superv; by jobcategory state; if s; run; /* Find the names of the supervisors. */ proc sort data=super; by empid; run; data names(drop=empid); merge super (in=super) staff (keep=empid firstname lastname); by empid; if super; run; proc print data=names noobs uniform; run;
proc sort data=super nodupkey;