---

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.

This sample is from the SAS Sample Library. For additional information refer to SAS Help and Online Documentation.

---

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: MERGE2                                              */
 /*   TITLE: Every Possible Merge Combination                    */
 /* PRODUCT: SAS                                                 */
 /*  SYSTEM: all                                                 */
 /*    KEYS: DATMAN DATASTEP SQL SET BY DSOPTION KEEP= IN= MERGE */
 /*          CARTESIAN PRODUCT FIRST. LAST. SUBSETTING IF SUM    */
 /*   PROCS: SORT SQL                                            */
 /*    DATA:                                                     */
 /*   NOTES: Version 6 supports PROC SQL, whose join operator    */
 /*          does the type of merge naturally. See the SQL       */
 /*          example at the bottom.                              */
 /*                                                              */
 /* SUPPORT:                             UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC:                                                     */
 /*                                                              */
 /****************************************************************/
 
 /* The following data steps merge two SAS data sets       */
 /* creating all possible combinations of the BY variable. */
 /* Observations with no matching BY variable are deleted. */
 /*                                                        */
 /*                Kenneth M. Bodner                       */
 /*               Dow Chemical U.S.A.                      */


DATA PREG;
  INPUT NAME $ PREG_NUM BIRTHDAT YYMMDD6.;
  FORMAT BIRTHDAT YYMMDD8.;
  CARDS;
JONES 2 741202
CONNERS 1 600718
SMITH 1 620427
CONNERS 3 650926
NEELY 1 710111
JONES 3 770614
CONNERS 2 620809
;

PROC SORT; 
   BY NAME;
RUN;

PROC PRINT;
RUN;

DATA EXPO;
  INPUT NAME $ EXPOSDAT YYMMDD6. EXPOSLVL $;
  FORMAT EXPOSDAT YYMMDD8.;
  CARDS;
JONES 721230 LOW
SMITH 601017 HIGH
CONNERS 641112 LOW
CARSON 680119 MED
JONES 760618 MED
GRANT 710315 HIGH
CONNERS 651112 HIGH
;

PROC SORT; 
   BY NAME;
RUN;

PROC PRINT;
RUN;

DATA PREG1 PREG2(KEEP=NAME NPREG);
  SET PREG; 
  BY NAME;
  IF FIRST.NAME THEN NPREG=0;
  NPREG+1;
  OUTPUT PREG1;
  IF LAST.NAME THEN OUTPUT PREG2;
RUN;

PROC PRINT DATA=PREG1;
RUN;

PROC PRINT DATA=PREG2;
RUN;

DATA EXPO2(KEEP=NAME TOTEXPOS);
   SET EXPO; 
   BY NAME;
   IF FIRST.NAME THEN TOTEXPOS=0;
   TOTEXPOS+1;
   IF LAST.NAME THEN OUTPUT;
RUN;

PROC PRINT DATA=EXPO2;
RUN;

DATA BOTHID;
   MERGE PREG2(IN=INPREG) EXPO2(IN=INEXPOS); 
   BY NAME;
   IF INPREG AND INEXPOS;
RUN;

PROC PRINT;
RUN;

DATA PREG3;
   MERGE PREG1 BOTHID(IN=INBOTH DROP=NPREG); 
   BY NAME;
   IF INBOTH;
   DO I=1 TO TOTEXPOS;
     OUTPUT;
   END;
RUN;

PROC PRINT;
RUN;

PROC SORT; 
   BY NAME I NPREG;
RUN;

DATA EXPO3;
   MERGE EXPO BOTHID(IN=INBOTH); 
   BY NAME;
   IF INBOTH;
   DO I=1 TO NPREG;
      OUTPUT;
   END;
RUN;

PROC PRINT;
RUN;

DATA FINAL;
   MERGE PREG3 EXPO3;
   DROP NPREG TOTEXPOS I;
RUN;

PROC PRINT;
RUN;


 /* All that code to do a SQL join! */
 /* Compare with this.              */

proc sql;

   select p.name, preg_num, birthdat, exposdat, exposlvl
     from preg p, expo e
    where p.name = e.name;

quit;

---

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.

Sorted PREG data set

Obs     NAME      PREG_NUM    BIRTHDAT

 1     CONNERS        1       60-07-18
 2     CONNERS        3       65-09-26
 3     CONNERS        2       62-08-09
 4     JONES          2       74-12-02
 5     JONES          3       77-06-14
 6     NEELY          1       71-01-11
 7     SMITH          1       62-04-27


Sorted EXPO data set

Obs     NAME      EXPOSDAT    EXPOSLVL

 1     CARSON     68-01-19      MED
 2     CONNERS    64-11-12      LOW
 3     CONNERS    65-11-12      HIGH
 4     GRANT      71-03-15      HIGH
 5     JONES      72-12-30      LOW
 6     JONES      76-06-18      MED
 7     SMITH      60-10-17      HIGH


PREG1

Obs     NAME      PREG_NUM    BIRTHDAT    NPREG

 1     CONNERS        1       60-07-18      1
 2     CONNERS        3       65-09-26      2
 3     CONNERS        2       62-08-09      3
 4     JONES          2       74-12-02      1
 5     JONES          3       77-06-14      2
 6     NEELY          1       71-01-11      1
 7     SMITH          1       62-04-27      1


PREG2

Obs     NAME      NPREG

 1     CONNERS      3
 2     JONES        2
 3     NEELY        1
 4     SMITH        1


EXPO2

Obs     NAME      TOTEXPOS

 1     CARSON         1
 2     CONNERS        2
 3     GRANT          1
 4     JONES          2
 5     SMITH          1


BOTHID

Obs     NAME      NPREG    TOTEXPOS

 1     CONNERS      3          2
 2     JONES        2          2
 3     SMITH        1          1


PREG3

Obs     NAME      PREG_NUM    BIRTHDAT    NPREG    TOTEXPOS    I

  1    CONNERS        1       60-07-18      1          2       1
  2    CONNERS        1       60-07-18      1          2       2
  3    CONNERS        3       65-09-26      2          2       1
  4    CONNERS        3       65-09-26      2          2       2
  5    CONNERS        2       62-08-09      3          2       1
  6    CONNERS        2       62-08-09      3          2       2
  7    JONES          2       74-12-02      1          2       1
  8    JONES          2       74-12-02      1          2       2
  9    JONES          3       77-06-14      2          2       1
 10    JONES          3       77-06-14      2          2       2
 11    SMITH          1       62-04-27      1          1       1


EXPO3

Obs     NAME      EXPOSDAT    EXPOSLVL    NPREG    TOTEXPOS    I

  1    CONNERS    64-11-12      LOW         3          2       1
  2    CONNERS    64-11-12      LOW         3          2       2
  3    CONNERS    64-11-12      LOW         3          2       3
  4    CONNERS    65-11-12      HIGH        3          2       1
  5    CONNERS    65-11-12      HIGH        3          2       2
  6    CONNERS    65-11-12      HIGH        3          2       3
  7    JONES      72-12-30      LOW         2          2       1
  8    JONES      72-12-30      LOW         2          2       2
  9    JONES      76-06-18      MED         2          2       1
 10    JONES      76-06-18      MED         2          2       2
 11    SMITH      60-10-17      HIGH        1          1       1


FINAL

Obs     NAME      PREG_NUM    BIRTHDAT    EXPOSDAT    EXPOSLVL

  1    CONNERS        1       60-07-18    64-11-12      LOW
  2    CONNERS        3       65-09-26    64-11-12      LOW
  3    CONNERS        2       62-08-09    64-11-12      LOW
  4    CONNERS        1       60-07-18    65-11-12      HIGH
  5    CONNERS        3       65-09-26    65-11-12      HIGH
  6    CONNERS        2       62-08-09    65-11-12      HIGH
  7    JONES          2       74-12-02    72-12-30      LOW
  8    JONES          3       77-06-14    72-12-30      LOW
  9    JONES          2       74-12-02    76-06-18      MED
 10    JONES          3       77-06-14    76-06-18      MED
 11    SMITH          1       62-04-27    60-10-17      HIGH


SQL results

NAME      PREG_NUM  BIRTHDAT  EXPOSDAT  EXPOSLVL
------------------------------------------------
CONNERS          1  60-07-18  64-11-12  LOW
CONNERS          1  60-07-18  65-11-12  HIGH
CONNERS          3  65-09-26  64-11-12  LOW
CONNERS          3  65-09-26  65-11-12  HIGH
CONNERS          2  62-08-09  64-11-12  LOW
CONNERS          2  62-08-09  65-11-12  HIGH
JONES            2  74-12-02  72-12-30  LOW
JONES            2  74-12-02  76-06-18  MED
JONES            3  77-06-14  72-12-30  LOW
JONES            3  77-06-14  76-06-18  MED
SMITH            1  62-04-27  60-10-17  HIGH

This example shows every possible merge combination.

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

Date Modified:	2005-12-08 11:34:45
Date Created:	2005-05-23 13:49:24

Operating System and Release Information

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