*Copyright @ www.statschoice.com;

/********************************************************************************

What is the word meaning of 'merge'?

********************************************************************************/

/********************************************************************************

combine to form a single entity.

********************************************************************************/

/********************************************************************************

Common scenarios where merging (joining rows into a single observation) is required

********************************************************************************/

/********************************************************************************

Presenting height and weight of students on a single observation when height and weight is stored in two different datasets
Presenting height, weight and sex of students on a single observation when height, weight and sex is stored in three different datasets
Presenting treatment start date of a subject along side of adverse event start date to check if the event started after treatment start date

********************************************************************************/

/********************************************************************************

What does 'MERGE statement' of SAS data step do?

********************************************************************************/

/********************************************************************************

By definition, merge statement is used for joining rows from two or more datasets into a single row

********************************************************************************/

/********************************************************************************

What is 'match merging' in SAS?

********************************************************************************/

/********************************************************************************

Combining rows from two or more datasets into a single observation based on the values of one or more common variables is called match merging
When performing a match merge, we need to specify the common variables, based on which we need to merge the ovservations, on the by statement

********************************************************************************/

/********************************************************************************

Creating some sample datasets

********************************************************************************/

data  height(keep=name height)
        weight(keep=name weight)
        sex(keep=name sex)
        age(keep=name age);
     set sashelp.class;
  run;

/********************************************************************************

One to One Match Merging

********************************************************************************/

/********************************************************************************

Create a dataset named heightweight by merging height and weight datasets based on the values present in name variable

********************************************************************************/

/********************************************************************************

When perorming a match merge, we need to sort the input datasets based on the common variables
We need to specify the name of the output dataset on the data statement
We need to specify the names of the input datasets, separated by a space, on the merge statement
As we are performing a match merge, we need to specify the common variables based on whose values we want to join the observations on the by statement. Here in this example, as we need to join the height and weight information of each student we need to specify the variable holding the names of the students as by variable

********************************************************************************/

proc sort data=height;
     by name;
  run;
   
  proc sort data=weight;
     by name;
  run;
   
  data heightweight;
     merge height weight;
     by name;
  run;

239   proc sort data=height;
  240      by name;
  241   run;
   
  NOTE: There were 19 observations read from the data set WORK.HEIGHT.
  NOTE: The data set WORK.HEIGHT has 19 observations and 2 variables.
  NOTE: PROCEDURE SORT used (Total process time):
        real time           0.01 seconds
        cpu time            0.00 seconds
   
   
  242
  243   proc sort data=weight;
  244      by name;
  245   run;
   
  NOTE: There were 19 observations read from the data set WORK.WEIGHT.
  NOTE: The data set WORK.WEIGHT has 19 observations and 2 variables.
  NOTE: PROCEDURE SORT used (Total process time):
        real time           0.00 seconds
        cpu time            0.00 seconds
   
   
  246
  247   data heightweight;
  248      merge height weight;
  249      by name;
  250   run;
   
  NOTE: There were 19 observations read from the data set WORK.HEIGHT.
  NOTE: There were 19 observations read from the data set WORK.WEIGHT.
  NOTE: The data set WORK.HEIGHTWEIGHT has 19 observations and 3 variables.
  NOTE: DATA statement used (Total process time):
        real time           0.01 seconds
        cpu time            0.01 seconds

/********************************************************************************

Create a dataset named hws by merging height,weight and sex datasets based on the values present in name variable

********************************************************************************/

/********************************************************************************

Presort the input datasets based on the common variables using proc sort
Peform the merge of 3 input datasets based on the common variable, name

********************************************************************************/

proc sort data=height;
     by name;
  run;
   
  proc sort data=weight;
     by name;
  run;
   
  proc sort data=sex;
     by name;
  run;
   
  data hws;
     merge height weight sex;
     by name;
  run;

7736   proc sort data=height;
  7737      by name;
  7738   run;
   
  NOTE: There were 19 observations read from the data set WORK.HEIGHT.
  NOTE: The data set WORK.HEIGHT has 19 observations and 2 variables.
  NOTE: PROCEDURE SORT used (Total process time):
        real time           0.00 seconds
        cpu time            0.00 seconds
   
   
  7739
  7740   proc sort data=weight;
  7741      by name;
  7742   run;
   
  NOTE: There were 19 observations read from the data set WORK.WEIGHT.
  NOTE: The data set WORK.WEIGHT has 19 observations and 2 variables.
  NOTE: PROCEDURE SORT used (Total process time):
        real time           0.00 seconds
        cpu time            0.00 seconds
   
   
  7743
  7744   proc sort data=sex;
  7745      by name;
  7746   run;
   
  NOTE: There were 19 observations read from the data set WORK.SEX.
  NOTE: The data set WORK.SEX has 19 observations and 2 variables.
  NOTE: PROCEDURE SORT used (Total process time):
        real time           0.00 seconds
        cpu time            0.00 seconds
   
   
  7747
  7748   data hws;
  7749      merge height weight sex;
  7750      by name;
  7751   run;
   
  WARNING: Multiple lengths were specified for the BY variable NAME by input data sets. This might cause unexpected results.
  NOTE: There were 19 observations read from the data set WORK.HEIGHT.
  NOTE: There were 19 observations read from the data set WORK.WEIGHT.
  NOTE: There were 19 observations read from the data set WORK.SEX.
  NOTE: The data set WORK.HWS has 19 observations and 4 variables.
  NOTE: DATA statement used (Total process time):
        real time           0.01 seconds
        cpu time            0.01 seconds

/********************************************************************************

Create a dataset named ahws by merging age,height,weight and sex datasets based on the values present in name variable

********************************************************************************/

/********************************************************************************

Presort the input datasets based on the common variables using proc sort
Peform the merge of 4 input datasets based on the common variable, name

********************************************************************************/

proc sort data=age;
     by name;
  run;
   
  proc sort data=height;
     by name;
  run;
   
  proc sort data=weight;
     by name;
  run;
   
  proc sort data=sex;
     by name;
  run;
   
  data ahws;
     merge age height weight sex;
     by name;
  run;

15236  proc sort data=age;
  15237     by name;
  15238  run;
   
  NOTE: There were 19 observations read from the data set WORK.AGE.
  NOTE: The data set WORK.AGE has 19 observations and 2 variables.
  NOTE: PROCEDURE SORT used (Total process time):
        real time           0.00 seconds
        cpu time            0.00 seconds
   
   
  15239
  15240  proc sort data=height;
  15241     by name;
  15242  run;
   
  NOTE: There were 19 observations read from the data set WORK.HEIGHT.
  NOTE: The data set WORK.HEIGHT has 19 observations and 2 variables.
  NOTE: PROCEDURE SORT used (Total process time):
        real time           0.00 seconds
        cpu time            0.00 seconds
   
   
  15243
  15244  proc sort data=weight;
  15245     by name;
  15246  run;
   
  NOTE: There were 19 observations read from the data set WORK.WEIGHT.
  NOTE: The data set WORK.WEIGHT has 19 observations and 2 variables.
  NOTE: PROCEDURE SORT used (Total process time):
        real time           0.00 seconds
        cpu time            0.00 seconds
   
   
  15247
  15248  proc sort data=sex;
  15249     by name;
  15250  run;
   
  NOTE: There were 19 observations read from the data set WORK.SEX.
  NOTE: The data set WORK.SEX has 19 observations and 2 variables.
  NOTE: PROCEDURE SORT used (Total process time):
        real time           0.00 seconds
        cpu time            0.00 seconds
   
   
  15251
  15252  data ahws;
  15253     merge age height weight sex;
  15254     by name;
  15255  run;
   
  NOTE: There were 19 observations read from the data set WORK.AGE.
  NOTE: There were 19 observations read from the data set WORK.HEIGHT.
  NOTE: There were 19 observations read from the data set WORK.WEIGHT.
  NOTE: There were 19 observations read from the data set WORK.SEX.
  NOTE: The data set WORK.AHWS has 19 observations and 5 variables.
  NOTE: DATA statement used (Total process time):
        real time           0.00 seconds
        cpu time            0.00 seconds