[SOLVED] SAS - Determine if group of columns contains any value of a set

SAS - Determine if group of columns contains any value of a set

I have a data set that contains several numbered columns, Code1, Code2, ... Code12. I also have a set of values for those codes %myCodes = ('ABC','DEF','GHI',etc.).

What I want to do is filter so that I include only rows where at least one of the Code1 through Code12 columns contains a value from myCodes. I realize that I could just do a very long OR condition [e.g. (Code1 in &myCodes or Code2 in &myCodes or ...)], but I was wondering if there is a more efficient way to do it.

Solution

You could put your columns that you want to select within a macro variable and loop over them all.

%let cols    = col1 col2 col3 col4;
%let myCodes = ('ABC','DEF','GHI');

%macro condition;
    %scan(&cols, 1) IN &myCodes

    %do i = 2 %to %sysfunc(countw(&cols));
        OR %scan(&cols, &i) IN &myCodes
    %end;
%mend;

data want;
    set have;
    where %condition;
run;

If you look at %condition, it has all of your filters:

%put %condition;

col1 IN ('ABC','DEF','GHI') OR col2 IN ('ABC','DEF','GHI') OR col3 IN ('ABC','DEF','GHI') OR col4 IN ('ABC','DEF','GHI')