/*  C03S1D01 */

/* 1. MERGE statement with BY statement in the DATA step */

libname ia 'c:\workshop\winsas\effi';

proc sort data = ia.schedule out = schedule;
  by RouteStart;
run;

proc sort data = ia.airport out =airport(keep = AirportCode City);
  by AirportCode;
run;

data schedule;
  merge schedule airport(rename = (AirportCode = RouteStart
                                                      City = Startcity));
  by RouteStart;
run;

proc sort data = schedule;
  by RouteDestination;
run;

data schedule;
  merge schedule airport(rename = (AirportCode = RouteDestination
                                                      City = DestinationCity));
  by RouteDestination;
run;

/* 2. Inner join operation in PROC SQL */

proc sql;
  create table schedule as
	    select RouteStart,
		             AircraftID,
		             FlightNumber,
		             a1.City as StartCity,
		             RouteDestination,
                 A2.City as DestinationCity,	
		             StartTimeAdjust,
		             DestinationTimeAdjust,	
		             ScheduledDepartureTime,
		             ScheduledArrivalTime,
	               FirstClassCapacity,
           		   BusinessCapacity,
		             EconomyCapacity,
		             TotalPassengerCapacity,
		             CargoCapacity,
		             ScheduledDate
	      from ia.schedule,
 		             ia.airport a1,
		             ia.airport a2
		       where schedule.RouteStart = a1.AirportCode and
                      schedule.RouteDestination = a2.AirportCode;
quit;