/****************************************************************/
                     /*            S A S   P R O G R A M M I N G   II
         */
                     /*
         */
                     /*                     FINAL PROJECT
         */
                     /*
         */
                     /*         NAME: ONE-WAY TRIP ARRANGEMENT
         */
                     /*    PROCEDURE: PROC SQL,  MACRO, PUT
         */
                     /*     DATA SET: AIRLINE.DAT
         */
                     /*
         */
                     /*              BY XIAO CHEN AND DONGHUI YANG
         */
                     /*
         */

/****************************************************************/



/*-----------------------------------------------------------------------------------------------*/
   /*-- New Features in the project:
                      --*/
   /*--
                      --*/
   /*--   PROC SQL NOPRINT NUMBER;
                      --*/
   /*--     CREATE TABLE LIKE table-name;
                      --*/
   /*--     ALTER TABLE table-name
                      --*/
   /*--         <MODIFY column-definition<,column-definition>...>
                      --*/
   /*--         <DROP column-name<,column-name>...>;
                      --*/
   /*--     INSERT INTO table-name         <(column-name <,column-name>
...)>                     --*/
   /*--          SET-clause <set-clause> ...;
                      --*/
   /*--          VALUES-clause <values-clause> ...;
                      --*/
   /*--          SELECT    query-expression;
                      --*/
   /*--     DELETE
                      --*/
   /*--          FROM table-name
                      --*/
   /*--          <WHERE sql-expression>;
                      --*/
   /*--     SELECT xxx INTO: variable-name FROM table-name;
                      --*/
   /*--     SELECT DISTINCT variable-name FROM table-name;
                      --*/
   /*--     UPDATE table-name
                      --*/
   /*--          set-clause
                      --*/
   /*--          <where-expression>;
                      --*/
   /*--     DROP TABLE table-name;
                      --*/
   /*--   QUIT;
                      --*/
   /*--   Use automatic macro variable &SQLOBS
                      --*/

/*-----------------------------------------------------------------------------------------------*/




DATA AIRLINE;
INPUT FLTNO 3. DEPCITY $ 7-17 ARVCITY $ 22-32  @33 DEPTIME TIME5. @41
ARVTIME TIME5.;
CARDS;
747   RALEIGH        ATLANTA     7:20    8:30
534   ATLANTA        TAMPA       9:30   10:30
149   TAMPA          CHICAGO    12:00   13:30
361   RALEIGH        CHICAGO    10:00   11:10
597   CHICAGO        TAMPA       9:30   10:45
366   CHICAGO        RALEIGH    14:30   16:00
199   TAMPA          ATLANTA    13:00   14:00
305   ATLANTA        CINCINNATI 10:00   11:00
921   CINCINNATI     TAMPA      12:30   14:00
654   CHICAGO        CINCINNATI 15:00   16:00
;
RUN;
                   /*-- First we want to format the departure and arrival
time by using ALTER MODIFY --*
                   /*-- then we add two airlines to the original dataset by
using INSERT --*/

OPTIONS NODATE NONUMBER LS=84;
TITLE;FOOTNOTE;

PROC SQL NUMBER;
TITLE ' DATASET AIRLINE';
SELECT * FROM AIRLINE;

ALTER TABLE AIRLINE
   MODIFY DEPTIME FORMAT=TIME5., ARVTIME FORMAT=TIME5.;
SELECT * FROM AIRLINE;

INSERT INTO AIRLINE
  VALUES (622, 'CINCINNATI','AUSTIN','17:45't,'20:10't);
INSERT INTO AIRLINE
  SET   FLTNO=219,
        DEPCITY='TAMPA',
        ARVCITY='AUSTIN',
        DEPTIME='9:30't,
        ARVTIME='10:45't;
SELECT * FROM AIRLINE;
QUIT;


/*-------------------------------------------------------------------------------------------*/

OPTIONS MACROGEN;
%MACRO ONEWAYTR(DATA=);
/*----------------------------------------------------------------*/
/*-- Macro parameters:                                          --*/
/*-- DATA  - name of input data set                             --*/
/*--                                                            --*/
/*-- Macro purpose:                                             --*/
/*-- Use PROC SQL to merge all possible trips in one day        --*/
/*-- together                                                   --*/
/*----------------------------------------------------------------*/
PROC SQL NOPRINT;
  CREATE TABLE TRIP1 AS
    SELECT FLTNO AS FLT1, DEPCITY AS DEPCITY1, ARVCITY AS ARVCITY1,
           DEPTIME AS DEPTIME1, ARVTIME AS ARVTIME1
    FROM &DATA;
  %LET N=2;
  SELECT MAX(COUNT(DISTINCT DEPCITY), COUNT(DISTINCT ARVCITY)) INTO:
MAXCITYN FROM &DATA;

  %DO %UNTIL( &NUMB =0 | &N=%EVAL(&MAXCITYN+1));          /*--
'&N=&MAXCITYN' used to protect the loop --*/
    %LET B=%EVAL(&N-1);                  /*-- When all the columns are
empty, the &NUMB will be zero --*/
    CREATE TABLE TRIP&N AS
      SELECT TRIP&B..*, FLTNO AS FLT&N , DEPCITY AS DEPCITY&N,ARVCITY AS
ARVCITY&N,
             DEPTIME AS DEPTIME&N, ARVTIME AS ARVTIME&N
      FROM TRIP&B LEFT JOIN &DATA
      ON   ARVCITY&B=DEPCITY  &   ARVTIME&B < DEPTIME;

    %DO M=1 %TO &B;                      /*-- Delete the round-way trips
--*/
      DELETE FROM TRIP&N
      WHERE DEPCITY&M=ARVCITY&N & DEPCITY&M NE '' & ARVCITY&N NE '';
    %END;

    SELECT COUNT(FLT&N) INTO: NUMB FROM TRIP&N;     /*-- Put the value of
COUNT(FLT&N) --*/
    %LET N=%EVAL(&N+1);                             /*-- into a macro
variable named NUMB --*/
  %END;
    CREATE TABLE FINAL AS SELECT TRIP&B..*, &B AS TRIPNUMB FROM TRIP&B ORDER
BY DEPCITY1;
QUIT;
%MEND;


/*-------------------------------------------------------------------------------------------*/




%MACRO SHWTRCHS(INDATA=,DEPT=,DEST=,TRANS=);
/*----------------------------------------------------------------*/
/*-- Macro parameters:                                          --*/
/*-- INDATA  - name of input data set                           --*/
/*--   DEPT  - departure city                                   --*/
/*--   DEST  - arrival city                                     --*/
/*--  TRANS  - transit number (optional)                        --*/
/*--                                                            --*/
/*-- Macro purpose:                                             --*/
/*-- Find the one-way trips that specified the departure city,  --*/
/*-- arrival city and the transfer times.                       --*/
/*----------------------------------------------------------------*/

%ONEWAYTR(DATA=&INDATA);
PROC SQL NOPRINT;
  SELECT AVG(TRIPNUMB) INTO: B FROM FINAL;

  %IF &TRANS NE   %THEN %DO;           /*-- If the transit number is
specified, then create a --*/
     %LET MXFLIGHT=%EVAL(&TRANS+1);    /*-- table named ROUTE that contains
the airlines required. --*/
     CREATE TABLE ROUTE AS
       SELECT  *  FROM TRIP&MXFLIGHT
       WHERE DEPCITY1=&DEPT AND ARVCITY&MXFLIGHT=&DEST ;
     %END;


  %ELSE %DO;      /*-- If the transit number is not specified --*/
     CREATE TABLE ROUTE LIKE FINAL;     /*-- CREATE TABLE LIKE create a
table that has  --*/
                                        /*-- the same variables but no
observations.    --*/

     %DO K=1 %TO &B;                /*-- Choose the airline that at the kth
flight it will  --*/
                                    /*-- arrive at the city you have
specified, then erase  --*/
                                    /*-- the values from the (k+1)th to the
last column.    --*/
        INSERT INTO ROUTE
           SELECT * FROM FINAL
           WHERE DEPCITY1= &DEPT  AND  ARVCITY&K = &DEST;

        %DO KK=%EVAL(&K+1) %TO &B;
           UPDATE ROUTE
             SET FLT&KK=NULL,
                 DEPCITY&KK='',
                 ARVCITY&KK='',
                 DEPTIME&KK=NULL,
                 ARVTIME&KK=NULL;
        %END;

     %END;

     %LET MXFLIGHT=&B;
     %DO G=&B %TO 1 %BY -1;
        SELECT COUNT(FLT&G) INTO: TEST FROM ROUTE; /*-- Check whether there
is totally empty column. If yes, --*/
        %IF &TEST=0 %THEN %DO;                     /*--  then drop them and
count how many columns left. --*/
           ALTER TABLE ROUTE
           DROP FLT&G, DEPCITY&G, ARVCITY&G, DEPTIME&G, ARVTIME&G;
           %LET MXFLIGHT=%EVAL(&MXFLIGHT-1);
        %END;
     %END;

                                        /*-- Get the unique rows by using
INTERSECT --*/
     CREATE TABLE ROUTE AS
       SELECT * FROM ROUTE
       INTERSECT
       SELECT * FROM ROUTE;

  %END;
                                /*-- Get the row's number put into macro
variable RESULT --*/
                                /*-- Note: The number of how many flights
has been put into &MXFLIGHT --*/
   %LET RESULT=&SQLOBS;

OPTIONS LS=80;
     TITLE1 'One-way trip arrangement';
     TITLE2 "from &DEPT to &DEST";
     TITLE3 " You have &RESULT options";
     TITLE4 ' ';
     TITLE5 "- Provided by (name) on &sysday &sysdate -";
     TITLE6 ' ';   TITLE7 ' ';
     FOOTNOTE ' Have a nice trip.';

PROC SQL;

  %IF &RESULT=0 %THEN %DO;               /*-- If no airlines have been
select. --*/
      CREATE TABLE SHOW( RESULT CHAR(35));
      INSERT INTO SHOW(RESULT)
         VALUES('Sorry, no flight is available.');
      SELECT RESULT FROM SHOW;
      %END;

  %ELSE %DO;                             /*-- Output the airlines by using
FILE , PUT statement --*/

      DATA SHOW; SET ROUTE;
       FILE PRINT ;
        PUT
          @5 76*'*'// @7 'Option  '_n_ @;

          %DO J=1 %TO &MXFLIGHT;
            IF FLT&J NE NULL THEN DO;
               PUT   @24 'Flight No. :' @37 FLT&J 10. -L /@30 'From : '
DEPCITY&J  @52 DEPTIME&J TIME5./
                    @32 'To : ' ARVCITY&J  @52 ARVTIME&J TIME5. // @24
35*'ƒ'/;
            END;
          %END;
        RETURN;
       RUN;
  %END;

PROC SQL;
DROP TABLE ROUTE, SHOW;
QUIT;

%MEND;


/*-------------------------------------------------------------------------------------------*/

   /*----------------------------------------------------------------*/
   /*-- Now apply macro SHWTRCHS on the dataset AIRLINE            --*/
   /*----------------------------------------------------------------*/

%SHWTRCHS(INDATA=AIRLINE,DEPT='TAMP',DEST='CINCINNATI',TRANS=);