topic Re: Investigate whether each value in Column A occurs somewhere in Column B. in SAS Enterprise Guide

Investigate whether each value in Column A occurs somewhere in Column B.

Sille — Thu, 29 Sep 2022 11:48:21 GMT

Hi! I am looking for some help with a SAS-code. In one data set I have two columns (A and B). I would like to check, whether each value in Column A occurs somewhere in Column B. Can you help me out?

Re: Investigate whether each value in Column A occurs somewhere in Column B.

Quentin — Thu, 29 Sep 2022 11:53:56 GMT

Please provide some example data, and the output you would want from that data. Also please show the code you have tried. This will help people help you.

Re: Investigate whether each value in Column A occurs somewhere in Column B.

Ksharp — Thu, 29 Sep 2022 12:09:26 GMT

Check INTERSCEPT and EXCEPT in PROC SQL:

proc sql;
select A from have
intersect
select B from have;
quit;

proc sql;
select A from have
except
select B from have;
quit;

Re: Investigate whether each value in Column A occurs somewhere in Column B.

FreelanceReinh — Thu, 29 Sep 2022 14:03:23 GMT

Hi @Sille and welcome to the SAS Support Communities!

You can also use a DATA step that stops as soon as an A value was not found in column B.

data _null_;
dcl hash h(dataset:'have(keep=b)');
h.definekey('b');
h.definedone();
do until(last);
  set have end=last;
  if h.check(key:a) then do;
    put A= 'does not occur in column B.';
    stop;
  end;
end;
put 'Success! All A values were found in column B.';
stop;
run;

Re: Investigate whether each value in Column A occurs somewhere in Column B.

ballardw — Thu, 29 Sep 2022 14:18:20 GMT

You likely will need to clearly define "occurs somewhere in column B".

Suppose you have a value for A of "apple".

Does that "occur" in B if B has a value of "Apple" (case difference)? If B is "apple pear" (not equal to but is part of the string? If B is "APPLE pear" case and part of string? If B is "applesauce" part of the string but not a whole word? If B is "Applesauce Pear juice" case difference and part of a string but not a whole word?

If A is a more complex value such as "apple banana grape" then you get even more complex elements to consider such as all the components of A are in B but the order is different, in addition to the not word sub-string possibilities.