/*  Chapter 3  */

/*  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;

/*  C03S2D01.sas  */

/* 1. SET statement in the DATA step */

data schedule;
  set ia.Schedule ia.SESchedule;
run;

/* 2.  OUTER UNION CORRESPONDING operator in PROC SQL */

proc sql;
   create table schedule as
       select *
          from ia.Schedule
       outer union corr
       select *
          from ia.SESchedule;
quit;

/* 3.  PROC APPEND */
/*  DO NOT SUBMIT  */

proc append base=ia.Schedule data=ia.SESchedule;
run;

/* 4. INSERT INTO statement in PROC SQL */
/*  DO NOT SUBMIT  */

proc sql;
   insert into ia.Schedule
      select ' ' as AircraftID,
                FlightNumber,
                RouteNumber,
                RouteStart,
                RouteDestination,
                RouteDistance,
                . as StartTimeAdjust,
                . as DestinationTimeAdjust,
                ScheduledDepartureTime,
                ScheduledArrivalTime,
                FirstClassCapacity,
                . as BusinessCapacity,
                EconomyCapacity,
                TotalPassengerCapacity,
                CargoCapacity,
                ScheduledDate
         from ia.SESchedule;
quit;

/*  C03S5D01 */

data Schedule;
  set ia.Schedule ia.SESchedule open = defer;
run;

/*  C03S6D01  */

proc sort data = ia.Schedule out = Schedule;
  by FlightNumber ScheduledDate;
run;

proc sort data = ia.Updates out = Updates;
  by FlightNumber ScheduledDate;
run;

data Schedule;
  update Schedule Updates;
  by FlightNumber ScheduledDate;
run;