26018 - Combining observations when variable values do not match exactly

Sample 26018: Combining observations when variable values do not match exactly

/* Create sample data sets */

data one;
  input time time5.  sample;
  format time datetime13.;
  time=dhms('23nov94'd,0,0,time);
datalines;
09:01   100
10:03   101
10:58   102
11:59   103
13:00   104
14:02   105
16:00   106
;

data two;
  input time time5.  sample;
  format time datetime13.;
  time=dhms('23nov94'd,0,0,time);
datalines;
09:00   200
09:59   201
11:04   202
12:02   203
14:01   204
14:59   205
15:59   206
16:59   207
18:00   208
;

/* Create MATCH1.  Route execution to a group of statements that read an */
/* observation from ONE and then to another group that reads from TWO.   */

data match1 (keep = time1 time2 sample1 sample2);
  link getone;
  link gettwo;
  
  /* Format the datetime variables.  Set to 0 the two variables that will be */
  /* used to indicate that the last observation from data set ONE or TWO has */
  /* been both read and processed.                                           */
  format time1 time2 datetime13.;
  onedone=0;  twodone=0;

  /* Check the value of TEMPT1 against TEMPT2.  If there is less than a 5-   */
  /* minute (300 second) difference between them, assign the values of these */
  /* "temp" variables to the variables that you want to write to the output  */
  /* data set, and then write an observation.  Execute the LINK statements   */
  /* to read a new observation from ONE and from TWO.                        */
  do while (1=1);
    if abs(tempt1-tempt2) < 300 then
    do;
       time1=tempt1;
       time2=tempt2;
       sample1=temps1;
       sample2=temps2;
       output;
       link getone;
       link gettwo;
    end;

    /* If the difference between TEMPT1 and TEMPT2 is five minutes or more, */
    /* test for further conditions.  If the conditions are met, write an    */
    /* observation that contains the actual values from TWO but missing     */
    /* values from ONE.                                                     */
    else if (tempt1 > tempt2 and twodone=0) or onedone then
    do;
      time1=.;
      time2=tempt2;
      sample1=.;
      sample2=temps2;
      output;
      link gettwo;
    end;

    /* If conditions have not been met in the previous IF-THEN or ELSE-IF/THEN */
    /* statements, test for further conditions.  If the conditions are met,    */
    /* write an observation that contains the actual values from ONE but       */
    /* missing values from TWO.                                                */
    else if (tempt1 < tempt2 and onedone=0) or twodone then
    do;
      time1=tempt1;
      time2=.;
      sample1=temps1;
      sample2=.;
      output;
      link getone;
    end;

    /* When you have processed all observations from both ONE and TWO, */
    /* stop the DATA step.                                             */
    if onedone and twodone then stop;

  /* end the DO WHILE loop */
  end;      
  return;

  /* If there are more observations in ONE, read another observation.    */
  /* If the last observation has already been read, set ONEDONE to 1 to  */
  /* indicate that the last observation was both read and processed, and */
  /* then prevent the SET statement from executing and attempting to     */
  /* read past the end of data set ONE.                                  */
  getone: 
  if last1 then 
  do;
    onedone=1;
    return;
  end;
  
  set one (rename=(time=tempt1 sample=temps1)) end=last1;
  return;

  /* If there are more observations in TWO, read another observation.    */
  /* If the last observation has already been read, set TWODONE to 1 to  */
  /* indicate that the last observation was both read and processed, and */
  /* then prevent the SET statement from executing and attempting to     */
  /* read past the end of data TWO.                                      */
  gettwo: 
  if last2 then 
  do;
    twodone=1;
    return;
  end;

  set two (rename=(time=tempt2 sample=temps2)) end=last2;
  return;

run;


proc print data=match1;
  title 'MATCH1';
run;


/***********************************************************************/
/* Example 2: Related Technique                                        */
/*                                                                     */
/* The following PROC SQL step uses considerably less code to produce  */
/* the same output as the DATA step, although the rows and columns are */
/* in a different order in the resulting data set.                     */
/***********************************************************************/

data one;
  input time time5.  sample;
  format time datetime13.;
  time=dhms('23nov94'd,0,0,time);
datalines;
09:01   100
10:03   101
10:58   102
11:59   103
13:00   104
14:02   105
16:00   106
;

data two;
  input time time5.  sample;
  format time datetime13.;
  time=dhms('23nov94'd,0,0,time);
datalines;
09:00   200
09:59   201
11:04   202
12:02   203
14:01   204
14:59   205
15:59   206
16:59   207
18:00   208
;

proc sql;
  create table match2 as
    select *
      from one(rename=(time=time1 sample=sample1)) full join
           two(rename=(time=time2 sample=sample2))
              on abs(time1-time2)<=5*60;
quit;

proc print data=match2;
   title 'MATCH2';
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.

MATCH1                                                   

Obs            time1            time2    sample1    sample2

  1    23NOV94:09:01    23NOV94:09:00      100        200
  2    23NOV94:10:03    23NOV94:09:59      101        201
  3    23NOV94:10:58                .      102          .
  4                .    23NOV94:11:04        .        202
  5    23NOV94:11:59    23NOV94:12:02      103        203
  6    23NOV94:13:00                .      104          .
  7    23NOV94:14:02    23NOV94:14:01      105        204
  8                .    23NOV94:14:59        .        205
  9    23NOV94:16:00    23NOV94:15:59      106        206
 10                .    23NOV94:16:59        .        207
 11                .    23NOV94:18:00        .        208



MATCH2                                                   

Obs            time1    sample1            time2    sample2

  1    23NOV94:09:01      100      23NOV94:09:00      200
  2    23NOV94:10:03      101      23NOV94:09:59      201
  3    23NOV94:11:59      103      23NOV94:12:02      203
  4    23NOV94:14:02      105      23NOV94:14:01      204
  5    23NOV94:16:00      106      23NOV94:15:59      206
  6                .        .      23NOV94:11:04      202
  7                .        .      23NOV94:14:59      205
  8                .        .      23NOV94:16:59      207
  9                .        .      23NOV94:18:00      208
 10    23NOV94:10:58      102                  .        .
 11    23NOV94:13:00      104                  .        .

Type:	Sample
Topic:	SAS Reference ==> Procedures ==> SQL SAS Reference ==> DATA Step SAS Reference ==> Statements ==> File-handling ==> SET Common Programming Tasks ==> Combining Data Data Management ==> Manipulation and Transformation ==> Combining and Modifying Data Sets SAS Reference ==> Statements ==> File-handling ==> MERGE

Date Modified:	2006-06-17 03:03:09
Date Created:	2006-02-06 16:25:37

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

Support

Sample 26018: Combining observations when variable values do not match exactly

Operating System and Release Information