BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ursula
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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,
Suryakiran

View solution in original post

4 REPLIES 4
ballardw
Super User

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
Pyrite | Level 9
Thank you so much for your prompt reply!!
I really appreciate it.
It works!
SuryaKiran
Meteorite | Level 14

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,
Suryakiran
ursula
Pyrite | Level 9

Thanks so much for the codes!

I like it and it works!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1563 views
  • 3 likes
  • 3 in conversation