Matching data

[ursula]

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.

[ballardw]

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.

[ursula]

Thank you so much for your prompt reply!!
I really appreciate it.
It works!

[SuryaKiran · Accepted solution]

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;

[ursula]

Thanks so much for the codes!

I like it and it works!