topic Re: Matching data in SAS Programming

Matching data

ursula — Fri, 13 Oct 2017 19:52:08 GMT

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.

Re: Matching data

ballardw — Fri, 13 Oct 2017 20:10:32 GMT

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.

Re: Matching data

SuryaKiran — Fri, 13 Oct 2017 23:43:05 GMT

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;

Re: Matching data

ursula — Fri, 13 Oct 2017 20:42:49 GMT

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

Re: Matching data

ursula — Fri, 13 Oct 2017 20:44:30 GMT

Thanks so much for the codes!

I like it and it works!