---

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: BUG17U01                                          */
  /*   TITLE: Merging SAS Data Sets, Chapter 17                 */
  /* PRODUCT: BASE                                              */
  /*  SYSTEM: ALL                                               */
  /*    KEYS: DOC DATASTEP PRINT TIME MERGE DSOPTION DROP=      */
  /*          RENAME= SORT BY                                   */
  /*   PROCS: PRINT SORT                                        */
  /*    DATA:                                                   */
  /*                                                            */
  /* SUPPORT:                             UPDATE:               */
  /*     REF: SAS Language and Procedures:  Usage               */
  /*    MISC:                                                   */
  /*                                                            */
  /**************************************************************/

  /* For simplicity the infile statement has been removed and */
  /* the raw data is being read into this step via a cards    */
  /* statement.                                               */

options ls=72;
title;
data class;
   input name $ 1-25 year $ 26-34 major $ 36-50;
cards;
Abbott, Jennifer         freshman
Carter, Tom              junior    Theater
Kirby, Elissa            senior    Mathematics
Tucker, Rachel           freshman
Uhl, Roland              sophomore
Wacenske, Maurice        junior    Theater
;
proc print data=class;
   title 'The Data Set CLASS';
run;

data timeslot;
   input date date7. @10 time $ @17 room $;
   format date date7.;
cards;
12sep88  10:00  103
12sep88  10:30  103
12sep88  11:00  207
13sep88  10:00  105
13sep88  10:30  105
15sep88  11:00  207
;
proc print data=timeslot;
   title 'Dates, Times, and Locations of Conferences';
run;

data schedule;
   merge class timeslot;
run;
proc print data=schedule;
   title 'Merging CLASS and TIMESLOT to Assign';
   title2 'Conference Times to Students';
run;

data class2;
   input name $ 1-25 year $ 26-34 major $ 36-50;
cards;
Hitchcock-Tyler, Erin    sophomore
Keil, Deborah            junior    Theater
Nacewicz, Chester        junior    Theater
Norgaard, Rolf           sophomore
Prism, Lindsay           senior    Anthropology
Singh, Rajiv             sophomore
Wittich, Stefan          junior    Physics
;
proc print data=class2;
   title 'The Data Set CLASS2';
run;

data exercise;
   merge class (drop=year major)
         class2 (drop=year major rename=(name=name2))
         timeslot;
run;
proc print data=exercise;
   title 'Merging Three Data Sets While Dropping and Renaming';
   title2 'Some Variables';
run;

data company;
   input name $ 1-25 age 27-28 sex $ 30;
cards;
Vincent, Martina          34 F
Phillipon, Marie-Odile    28 F
Gunter, Thomas            27 M
Harbinger, Nicholas       36 M
Benito, Gisela            32 F
Rudelich, Herbert         39 M
Sirignano, Emily          12 F
Morrison, Michael         32 M
;
proc sort data=company;
   by name;
run;
proc print data=company;
   title 'The Data Set COMPANY Sorted by NAME';
run;
data finance;
   input ssn $ 1-11 name $ 13-40 salary;
cards;
074-53-9892 Vincent, Martina             35000
776-84-5391 Phillipon, Marie-Odile       29750
929-75-0218 Gunter, Thomas               27500
446-93-2122 Harbinger, Nicholas          33900
228-88-9649 Benito, Gisela               28000
029-46-9261 Rudelich, Herbert            35000
442-21-8075 Sirignano, Emily             5000
;
proc sort data=finance;
   by name;
run;
proc print data=finance;
   title 'The Data Set FINANCE Sorted by NAME';
run;

data compfin;
   merge company finance;
   by name;
run;
proc print data=compfin;
   title 'Merging COMPANY and FINANCE';
run;

data reptory;
   input play $ 1-23 role $ 25-48 ssn $ 50-60;
cards;
No Exit                 Estelle                  074-53-9892
No Exit                 Inez                     776-84-5391
No Exit                 Valet                    929-75-0218
No Exit                 Garcin                   446-93-2122
Happy Days              Winnie                   074-53-9892
Happy Days              Willie                   446-93-2122
The Glass Menagerie     Amanda Wingfield         228-88-9649
The Glass Menagerie     Laura Wingfield          776-84-5391
The Glass Menagerie     Tom Wingfield            929-75-0218
The Glass Menagerie     Jim O'Connor             029-46-9261
The Dear Departed       Mrs. Slater              228-88-9649
The Dear Departed       Mrs. Jordan              074-53-9892
The Dear Departed       Henry Slater             029-46-9261
The Dear Departed       Ben Jordan               446-93-2122
The Dear Departed       Victoria Slater          442-21-8075
The Dear Departed       Abel Merryweather        929-75-0218
;
proc print data=reptory;
   title 'The Data Set REPTORY';
run;

proc sort data=finance;
   by ssn;
run;
proc sort data=reptory;
   by ssn;
run;
proc print data=finance;
   title 'The Data Set FINANCE Sorted by SSN';
run;
proc print data=reptory;
   title 'The Data Set REPTORY Sorted by SSN';
run;

data finrep;
   merge finance reptory;
   by ssn;
run;
proc print data=finrep;
   title 'Adding Players'' Names to REPTORY';
   title2 'by';
   title3 'Merging It with FINANCE';
run;

data newrep (drop=ssn);
   merge finance (drop=salary) reptory;
   by ssn;
run;
proc print data=newrep;
   title 'Replacing Players'' Social Security Numbers';
   title2 'with Their Names';
run;

proc sort data=finance;
   by name;
run;
proc sort data=company;
   by name;
run;

   /* Merge COMPANY and FINANCE into a */
   /* temporary data set.              */
data temp;
   merge company finance;
   by name;
run;
proc sort data=temp;
   by ssn;
run;

   /* Merge the temporary data set with REPTORY */
data all;
   merge temp reptory;
   by ssn;
run;
proc print data=all;
   title 'Merging Three Data Sets that Lack a Common Variable';
run;

data company2;
   input name $ 1-25 age 27-28 sex $ 30;
cards;
Benito, Gisela            32 F
Gunter, Thomas            27 M
Harbinger, Nicholas       36 M
Phillipon, Marie-Odile    28 F
Rudelich, Herbert         39 M
Sirignano, Emily          12 F
Vincent, Martina          34 F
;
proc print data=company2;
   title 'The Data Set COMPANY2';
run;
proc print data=finance;
   title 'The Data Set FINANCE';
run;

   /* One-to-one merge */
data onetoone;
   merge company2 finance;
run;
proc print data=onetoone;
   title 'Using a One-to-One Merge to Combine';
   title2 'COMPANY2 and FINANCE';
run;

   /* Match-merge */
data match;
   merge company2 finance;
   by name;
run;
proc print data=match;
   title 'Using a Match-Merge to Combine';
   title2 'COMPANY2 and FINANCE';
run;

data badmerge;
   merge company finance;
run;
proc print data=badmerge;
   title 'Using a One-to-One Merge Instead of a Match-Merge';
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.

These samples are from the "SAS Language and Procedures: Usage" book (No. 56075), Chapter 17. For output and additional information on the samples refer to this book.

Type:	Sample
Topic:	SAS Reference ==> DATA Step SAS Reference ==> Statements ==> File-handling ==> MERGE

Date Modified:	2005-12-08 11:34:44
Date Created:	2005-05-23 13:46:21

Operating System and Release Information

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	Base SAS	All	n/a	n/a