Checking if any value in one column matches any value in another column by id

Question

I'm trying to determine if any values in column a matches with any dates in column b. The issue is that I would like this to be grouped by participant id as well, so that I'm comparing columns by participant.

I tried to do this via proc sql. However, I couldn't figure out how to group by id since I wasn't using an aggregate function.

proc sql;
    create table match as
    select *, case when
            a in (select distinct b from allsurveys) then "yes"
            else "no"
            end as match
    from dataset;
quit;

Below is an example of the dataset: (https://i.sstatic.net/ejnTe.png)

What I would like: (https://i.sstatic.net/XsL3e.png)

Answer 1

Welcome :-)

Here is a non-sql approach

data have;
input id a b;
datalines;
1 12 100 
1 20 12  
1 65 65  
2 33 11  
2 45 5   
2 19 7   
3 89 51  
3 51 89  
3 77 77  
;

data want;

   if _N_ = 1 then do;
      dcl hash h(dataset : 'have');
      h.definekey('b');
      h.definedone();
   end;

   do until (last.id);
      set have;
      by id;
      match = max(match, h.check(key : a) = 0);
   end;
   
   do until (last.id);
      set have;
      by id;
      output;
   end;

run;

Result:

id  a   b   match
1   12  100 1
1   20  12  1
1   65  65  1
2   33  11  0
2   45  5   0
2   19  7   0
3   89  51  1
3   51  89  1
3   77  77  1