Sample 25329: Merging SAS Data Sets, Chapter 17
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
SAS System | Base SAS | All | n/a | n/a |