Hi there,
This dataset I named it as "Matching file"
ID Age A B C
M1 20 10 Yes No
M2 25 12 No Yes
M3 30 5 Yes Yes
M4 40 50 Yes Yes
M5 50 25 No No
This dataset I named it as "Searching file"
ID Age A B C
A100 25 10 Yes No -------------------matching M1
A200 25 5 Yes Yes --------------------matching M3
A300 30 12 No Yes ---------------------matching M2
A400 35 12 No Yes ---------------------No matching
A500 40 30 Yes Yes ---------------------No matching
A600 40 50 Yes Yes ---------------------matching M4
A700 35 50 Yes Yes ----------------------matching M4
A800 50 25 Yes No ----------------------No matching
I want to search for which IDs in "Searching file" are matching with the IDs in "Matching file", the Age can be +/- 5 from the "matching file", but the variables A,B and C have to exactly the same value.
My question is: how do I code it in SAS so I can output the Matching data, as follow:
This is what I want:
ID Age A B C
A100 25 10 Yes No
A200 25 5 Yes Yes
A300 30 12 No Yes
A600 40 50 Yes Yes
A700 35 50 Yes Yes
Thank you so much in advance.
DATA MATCHING;
INFILE DATALINES;
INPUT ID_MTCH :$ Age_MTCH: 8. A :8. B :$8. C :$8.;
DATALINES;
M1 20 10 Yes No
M2 25 12 No Yes
M3 30 5 Yes Yes
M4 40 50 Yes Yes
M5 50 25 No No
;
run;
DATA Searching;
INFILE DATALINES;
INPUT ID :$ Age: 8. A :8. B :$8. C :$8.;
DATALINES;
A100 25 10 Yes No
A200 25 5 Yes Yes
A300 30 12 No Yes
A400 35 12 No Yes
A500 40 30 Yes Yes
A600 40 50 Yes Yes
A700 35 50 Yes Yes
A800 50 25 Yes No
;
run;
proc sort data=matching;
by A B C;
proc sort data=searching;
BY A B C;
DATA WANT(KEEP=ID Age A B C) ;
MERGE MATCHING(in=match) Searching(IN=search);
BY A B C;
IF MATCH=1 and search=1 and ABS(age_mtch-age)<=5;
run;
This could be one way.
proc sql; create table want as select a.* from Searchfile as a left join Matchingfile as b on a.a = b.a and a.b = b.b and a.c = b.c where abs(a.age-b.age) le 5; quit;
Searchfile and Matchingfile should be your actual dataset names. The notation involving the a. and b. are shorthand for Searchfile.a meaning the variable a in the data set Searchfile. The "as a" assigns the shortcut name, it isn't magical what they should be as long as you keep them straight.
The logic is to match all of the a, b and c variable and then keep the record where the difference in the ages is <= to 5. If you haven't used Proc SQL the QUIT is used instead of Run to indicate the end of the procedure.
DATA MATCHING;
INFILE DATALINES;
INPUT ID_MTCH :$ Age_MTCH: 8. A :8. B :$8. C :$8.;
DATALINES;
M1 20 10 Yes No
M2 25 12 No Yes
M3 30 5 Yes Yes
M4 40 50 Yes Yes
M5 50 25 No No
;
run;
DATA Searching;
INFILE DATALINES;
INPUT ID :$ Age: 8. A :8. B :$8. C :$8.;
DATALINES;
A100 25 10 Yes No
A200 25 5 Yes Yes
A300 30 12 No Yes
A400 35 12 No Yes
A500 40 30 Yes Yes
A600 40 50 Yes Yes
A700 35 50 Yes Yes
A800 50 25 Yes No
;
run;
proc sort data=matching;
by A B C;
proc sort data=searching;
BY A B C;
DATA WANT(KEEP=ID Age A B C) ;
MERGE MATCHING(in=match) Searching(IN=search);
BY A B C;
IF MATCH=1 and search=1 and ABS(age_mtch-age)<=5;
run;
Thanks so much for the codes!
I like it and it works!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.