SQL: Alternative to using multiple 'AND' statements to find if columns contain same value)?

PharmlyDoc — Mon, 22 Nov 2021 23:13:19 GMT

I would like to see if multiple columns all contain '0' without having to use multiple 'AND' Statements.

It would ultimately be used within a CASE WHEN statement.

Proc SQL;
select
PAT_ID,
COL2, COL3, COL4, COL5, COL6, COL7,
CASE WHEN ((COL3 = 0) AND (COL4=0) AND (COL5=0) AND (COL6=0) AND (COL7=0)) THEN 1 ELSE 0 END ZEROS
FROM data;
quit;

Re: SQL: Alternative to using multiple 'AND' statements to find if columns contain same value)?

Reeza — Mon, 22 Nov 2021 23:20:59 GMT

Mathematical logic alternatives

Max of the series = Min of the series = 0
Range =0 and any value = 0
Add values and check if sum is 0
Use WHICHN to search for 1, if the alternative values are 1. If they're something else this option doesn't work

The third option is probably the easiest. If you run into floating point errors you may want to use a ROUND to ensure it doesn't happen or FUZZ but the idea is as follows:

CASE WHEN sum(col3, col4, col5, col6, col7) = 0THEN 1 ELSE 0 END ZEROS

@PharmlyDoc wrote:

I would like to see if multiple columns all contain '0' without having to use multiple 'AND' Statements.

It would ultimately be used within a CASE WHEN statement.
Proc SQL;
select
PAT_ID,
COL2, COL3, COL4, COL5, COL6, COL7,
CASE WHEN ((COL3 = 0) AND (COL4=0) AND (COL5=0) AND (COL6=0) AND (COL7=0)) THEN 1 ELSE 0 END ZEROS
FROM data;
quit;

topic SQL: Alternative to using multiple 'AND' statements to find if columns contain same value)? in SAS Programming

SQL: Alternative to using multiple 'AND' statements to find if columns contain same value)?

Re: SQL: Alternative to using multiple 'AND' statements to find if columns contain same value)?