Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile

• Print  |
• Feedback  |
• Share

  • E-mail

  
  
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLFUN05                                            */
 /*   TITLE: Demonstrates Merging Tables with Proc SQL           */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN WHERE SELECT SUBSTR MERGE SORT           */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /* SUPPORT:                             UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC: This example was contributed by Jack Hamilton       */
 /*          of Amdahl Corporation, via BITNET.                  */
 /*                                                              */
 /*          It demonstrates how PROC SQL can often provide      */
 /*          a more concise solution to data processing          */
 /*          problems.                                           */
 /*                                                              */
 /****************************************************************/
  
 
 /* 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;

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

cno       name               pno       desc                    qty
------------------------------------------------------------------
C001      Cust One           P001      Part One                 10
C001      Cust One           P002      Part Two                 20
C002      Cust Two           P002      Part Two                 20
C002      Cust Two           P003      Part Three               30
C003      Cust Three         P003      Part Three               50



Obs    cno    pno    qty       name          desc

 1     001    001     10    Cust One      Part One
 2     001    002     20    Cust One      Part Two
 3     002    002     20    Cust Two      Part Two
 4     002    003     30    Cust Two      Part Three
 5     003    003     50    Cust Three    Part Three

Operating System and Release Information