sqlfun05.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLFUN05                                            */
 /*   TITLE: fun/interesting applications of PROC SQL. (fun05)   */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN WHERE SELECT SUBSTR MERGE SORT           */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /*                                                              */
 /* SUPPORT: pmk                         UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC: this example shows how PROC SQL can often provide   */
 /*          a more concise solution to data processing          */
 /*          problems.                                           */
 /*                                                              */
 /****************************************************************/

 title1 'SAS SAMPLE LIBRARY, MEMBER(SQLFUN05)';

 /*
 *   your mission(should you choose....) is to merge three tables,
 *   but .....
 *
 *       the "common" variable has a different name in each table
 *
 *       the "common" variable has a different format....
 *
 *       parts and customers have no P/C prefix in some tables
 *       but they do in others.
 *
 */


data orders;
 input cno $ pno $ qty;
 cards;
C001 P001 10
C001 P002 20
C002 P003 30
C002 P002 20
C003 P003 50
;


data parts;
 input no $ desc $ 4-20;
 cards;
001 Part One
002 Part Two
003 Part Three
;

data cust;
 input no $ name $ 4-20;
 cards;
001 Cust One
002 Cust Two
003 Cust Three
;






  /*---------------------------------------------------------------*/
  /*-- the sql way...                                            --*/
  /*---------------------------------------------------------------*/

proc sql;

 select o.cno, c.name, o.pno, p.desc, o.qty
   from orders o, parts p, cust c
  where substr(cno, 2) = c.no
    and substr(pno, 2) = p.no;

quit;




  /*---------------------------------------------------------------*/
  /*-- the traditional way...                                    --*/
  /*---------------------------------------------------------------*/

data temp;
  set orders;
  cno = substr(cno, 2);
  pno = substr(pno, 2);
  run;

proc sort data=temp;
  by cno;
  run;

proc sort data=cust;
  by no;
  run;

data temp;
  merge temp(in=t) cust(in=c rename=(no=cno));
  by cno;
  if t and c;
  run;


proc sort data=temp;
  by pno;
  run;

proc sort data=parts;
  by no;
  run;

data temp;
  merge temp(in=t) parts(in=p rename=(no=pno));
  by pno;
  if t and p;
  run;

proc print;
  run;