Providing software solutions since 1976
Sample 26018: Combining observations when variable values do not match exactly
 |  |  |  |  |  |
Perform a fuzzy merge by merging observations from two data sets based on data values that do not exactly match.
Note:
This is Example 2.2 from Combining and Modifying SAS Data Sets - Examples. PROC SQL can also be used to accomplish this task. See Example 2 : Related Technique.
/* 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;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 . .
Perform a fuzzy merge by merging observations from two data sets based on data values that do not exactly match.
| 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 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | All | n/a | n/a |
