Example 4 - Many-to-many merge using supplement ID number as the unique identifier

This example is taken from the "Supplement" program and shows SAS code for merging the dataset, SUP123, which was created in Example 3, with Supplement file 4.  The unique identifier used to merge the data is the supplement ID number (DSDSUPID).  This example shows a many:many merge, meaning that both files being merged have multiple records per supplement.    


Program to Merge and Check Contents of Data

Sample Code

* Use the PROC SORT procedure and BY statement to sort the supplement 4   ;
* file (DSQ4_c).  Merge this file with the SUP123 file from the previous  ;
* step by using the PROC SQL procedure and the unique identifier,         ;
* DSDSUPID.  This merge will create a dataset called ALLSUP by adjoining  ;
* values from the supplement 4 file to the supplement 123 file, regardless;
* of the number of observations for DSDQUPID in each file.                ;
*                                                                         ;
* For more information on PROC SQL you may want to consult the SAS manual ;  

proc sort data =NH.DSQ4_C out <=DSQ4_C;
    by DSDSUPID;
run ;

proc sql ;
    create table ALLSUP as
        from DSQ4_C as H,
        SUP123 as M
        where H.DSDSUPID=M.DSDSUPID;
run ;

* Use the PROC CONTENTS procedure to list the contents of the SUP123      ;  
* dataset — the new merged dataset.  Use the VARNUM option to list the    ;
* variables according to their position in the dataset.                   ;
*                                                                         ;
* Use the PROC MEANS procedure to determine the number of non-missing     ;
* values (N), number of missing values (Nmiss), minimum (min) and maximum ;
*(max) values, to two decimal places (maxdec=2), for the variables in the ;
* merged dataset.                                                         ; 

proc contents
data = ALLSUP
varnum ;

proc means data = ALLSUP N Nmiss min max maxdec = 2 ;
run ;


Output of Program

Click here to view program output and highlights


In each of the Preparing an Analytic Dataset modules beginning with this module, you will be working with temporary datasets, which are saved in the WORK folder of your SAS program.  The dataset exists only as long as your SAS session and is deleted when you exit the program.  If you would like to save these datasets so that you can return to them at a later time, you can learn how to do this in the Save a Dataset module at the end of this course.


close window icon Close Window to return to module page.